Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 29th, 2008, 12:24 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Ottawa, , Canada.
Posts: 139
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query w/ arguments

Hi,

Is there a way to build queres where I could change the arguments via vba?

Say I have this query

SELECT COUNT(ID) FROM MyTable WHERE SubmitDt BETWEEN #01/02/2008# AND #29/02/2008#...

Next month, I would have to go into the query and change the date... but I would like to have a form, where I just choose the month/year and it will be able to modify the between date ranges...

Reply With Quote
  #2 (permalink)  
Old March 3rd, 2008, 10:02 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

There are several ways to do this. How do you want the user to select the date ranges?

You could have two combo boxes on your form that you populate with dates from your table data (set Unique values to yes). The user selects a data from the start range, then the second combo cascades only later dates, and the user selects the end date from that combo. Then on your query, rewrite it to lool like this:

SELECT COUNT(ID) FROM MyTable WHERE SubmitDt BETWEEN [Forms]![myForm].[cboStartDate] AND [Forms]![myForm].[cboEndDate]

You can also do this if you want to prompt the user at runtime:

SELECT COUNT(ID) FROM MyTable WHERE SubmitDt BETWEEN #[Please enter a start date:]# AND #[Please enter an end date]#

You can also make a form that has two combos, one of which has Month Names, and one of which has years. The Month combo would have two columns, like this:

Col0 Col1
1 January
2 February
3 March

etc.

Then when the user selects the month and year, do this with the code:

Dim iMonth As Integer
Dim iYear As Integer
Dim sLink As String
Dim sDoc As String

If IsNull(Me.cboMonth) or Me.cboMonth = "" Then
   MsgBox "Please select a month", vbCritical
   End Sub
Else
   iMonth = Me.cboMonth
End If

If IsNull(Me.cboYear) or Me.cboYear = "" Then
   MsgBox "Please select a Year", vbCritical
   End Sub
Else
   iMonth = Me.cboYear
End If

sLink = "DatePart("m", [MyDateField] = " & iMonth " AND DatePart("yyy", [MyDateField]) = " & iYear

sDoc = "rptMyReport"

DoCmd.etc

I may have gotten away from myself with that last one. That will work with SQL Server, but I am not sure if it will work with Acces. In that case, create two new fields in your query called: CurMonth and CurYear, and then put this in the Field:

CurMonth:DatePart("m", [MyDateField])

And then in the criteria, refer to the combo on your form. The same for the year.

You can see there are many ways to do this.

Did that help?




mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #3 (permalink)  
Old March 3rd, 2008, 10:04 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I sould mention, I had to do a similar thing with a report, and I know the report will only ever be run from this interface after the first of the month for the previous month. So I select the month and year automatically like this:

Dim iMonth As Integer
Dim iYear As Integer

iMonth = DatePart("m", Date()) - 1
iYear = DatePart("yyyy", Date())

etc.



mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple arguments MunishBhatia C# 2 December 8th, 2006 07:34 AM
Arguments snowy0 VB.NET 2002/2003 Basics 3 September 3rd, 2004 08:40 AM
Arguments error andris2000 VB Components 11 August 4th, 2004 05:54 AM
Query String Arguments kevind23 Classic ASP Basics 4 June 29th, 2004 10:16 PM
Program arguments bramtram VB How-To 1 August 14th, 2003 03:18 AM



All times are GMT -4. The time now is 05:24 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.