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 | 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 July 6th, 2007, 05:06 AM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Between And Specifier

I have a report with multiple subreports. Some subreports require input of one date [Month] and others require input of a date range [IncidentMonth]. I am using a form to enter the date range [FromDate] and [ToDate]. Can someone please help with the VBA code ... what I am using doesn't work.

DoCmd.OpenReport stDocName, acPreview, , "[IncidentMonth] Between Forms![frmRunReport]![FromDate] And Forms![frmRunReport]![ToDate] AND [Month] = Forms![frmRunReport]![FromDate]"

Thanks

Reply With Quote
  #2 (permalink)  
Old July 6th, 2007, 05:14 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi David,

You need to seperate the variables from the String, as shown below (kep points in bold)
Code:
DoCmd.OpenReport _
    stDocName, acPreview, , _
    "[IncidentMonth] Between #" & Format$(Forms![frmRunReport]![FromDate], "yyyy-mm-dd") & "# And #" & Format$(Forms![frmRunReport]![ToDate], "yyyy-mm-dd") & "# AND [Month] = #" & Format$(Forms![frmRunReport]![FromDate], "yyyy-mm-dd") & "#"
    Note, I have also formatted the dates to UDF format, and placed them in hashes (#) which is how Access recognises them as dates.

I hope this helps,

Rob
Reply With Quote
  #3 (permalink)  
Old July 6th, 2007, 07:01 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Not quite, Rob. Formatting is used only for the purposes of displaying something on a form or report. It is not used as a criteria for a query or as a WHERE condition.

Also, the date range is supposed to be a date, not just a month. Assuming [IncidentMonth] is a full date, then you simply need

DoCmd.OpenReport stDocName, acPreview, , _
    "[IncidentMonth] Between #" & Forms.frmRunReport.Form.FromDate & "# And #" & Forms.frmRunReport.Form.ToDate & "#"

Or if the code is already being called from frmRunReport, then

DoCmd.OpenReport stDocName, acPreview, , _
    "[IncidentMonth] Between #" & Me.FromDate & "# And #" & Me.ToDate & "#"

If IncidentMonth is truly a month and not a full date, then you need to do something like this, keeping in mind that the month is an integer (no # needed).

DoCmd.OpenReport stDocName, acPreview, , _
    "[IncidentMonth] Between " & Month(Me.FromDate) & " And " & Month(Me.ToDate)

Month() is a built-in Access function and should not be used as a field or variable name.
Reply With Quote
  #4 (permalink)  
Old July 6th, 2007, 07:09 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by SerranoG
 Not quite, Rob. Formatting is used only for the purposes of displaying something on a form or report. It is not used as a criteria for a query or as a WHERE condition.
 Why is this? UI elements often need to be reformatted, Access always stores data in MM/DD/YYYY format, so I was told its always a good idea to always ensure you format SQL strings, especially when working with dates.

Quote:
quote:Originally posted by SerranoG
Also, the date range is supposed to be a date, not just a month.
Good Spot! I missed the fact that it was only a month!!

Regards,
Rob
Reply With Quote
  #5 (permalink)  
Old July 6th, 2007, 07:17 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:UI elements often need to be reformatted, Access always stores data in MM/DD/YYYY format, so I was told its always a good idea to always ensure you format SQL strings, especially when working with dates.
It's one thing to reformat to display something or to find a criteria for a string, but not for data types of DATE.

A date is a date is a date to the computer... no matter how you format it. So that's why I said that when I saw that the criteria was a date.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #6 (permalink)  
Old July 6th, 2007, 07:33 AM
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by SerranoG
It's one thing to reformat to display something or to find a criteria for a string, but not for data types of DATE.

A date is a date is a date to the computer... no matter how you format it. So that's why I said that when I saw that the criteria was a date.
Not when the information being passed is a String.

Everyone (in the geeky world!) knows that dates are stored as numbers, hence the "date is a date is a date".
But SQL can only work against a text language, hence the formatting of the date is important.
The string has to be parsed into date serial by the data provider, get your month and the day around the wrong way, you've got problems...

Yes, IF the control formatting is set to the correct date format,
and the format is set correctly in the table design view, it will
work ok. But, its best practice to assume nothing with things like
this, and it has rapidly become the de facto standard to ALWAYS
work with dates in UDF format, Access and SQL Server (or most other
modern data providers) will always treat this String the same way.

I'm not trying to flame, but although what you are saying is right,
(as in dates are stored as serials), Access tries to be "smart" and
you can get some odd goings on with dates (you may not experience it
so much in the States). But, WHERE clauses are not "dates" they are
Strings, Strings must be parsed, and in order to parse correctly,
an expected format should be used.

Regards,
Rob

Reply With Quote
  #7 (permalink)  
Old July 8th, 2007, 11:31 PM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Gentlemen,
Thanks for your responses.
If I leave out the reference to [Month] and concentrate on (my original) where [IncidentMonth] is a date -
DoCmd.OpenReport stDocName, acPreview, , "[IncidentMonth] Between Forms![frmRunReport]![FromDate] And Forms![frmRunReport]![ToDate]"
I get a report with a page for each month (12 pages for a year).
If I use Rob's suggestion -
DoCmd.OpenReport _
    stDocName, acPreview, , _
    "[IncidentMonth] Between #" & Format$(Forms![frmRunReport]![FromDate], "yyyy-mm-dd") & "# And #" & Format$(Forms![frmRunReport]![ToDate], "yyyy-mm-dd") & "#"
I get exactly the same result.
If I use Serrano's suggestion -
DoCmd.OpenReport stDocName, acPreview, , _
    "[IncidentMonth] Between #" & Forms.frmRunReport.Form.FromDate & "# And #" & Forms.frmRunReport.Form.ToDate & "#"
I get a report with 8 pages ... the last 4 pages don't print?????????
If I use the criteria for [IncidentMonth] Between [FromDate] And [ToDate] in each query and manually insert the values at report run time I get a one page report which is what I want. Why the difference?

Reply With Quote
  #8 (permalink)  
Old July 9th, 2007, 07:14 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

First I have to ask what the field IncidentMonth actually is. Is it a full date or is it just a month?


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #9 (permalink)  
Old July 18th, 2007, 06:27 PM
Registered User
 
Join Date: Jul 2007
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Been away for a few days, hence the pause.

IncidentMonth is a date. I used a full date for simplicity, always the first day of the month.

Reply With Quote
  #10 (permalink)  
Old July 19th, 2007, 07:07 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

The code I gave you should work if the FROM and TO dates are correct. Something else is going on that I cannot see just from these messages. Perhaps if you posted the code for the PRINT button's ON CLICK event and you explained the source of the report and any groupings it has.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
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
disadvantages of protected access specifier kishore.dyn Java Basics 2 August 10th, 2006 03:31 PM



All times are GMT -4. The time now is 09:37 PM.


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