By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,331 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

Select multiple date fields from one table

P: n/a
I am trying to create a report by selecting three date fields from one
table with records between a start date and end date. I have tried to
adapt one of Allen Browne's scripts as follows but it gives me records
outside my date criteria - I guess my strSelect is wrong! I tried
strSelect = * but that didn't work either.

Private Sub CmndOK_Click()
Dim strReport As String 'Name of report to open.
Dim strWhere As String 'Where condition for OpenReport.
Dim strSelect As String
Dim strFrom As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "TermDeposits"

strSelect =
"Termdeposits.TermDeposit1MaturityDate,Termdeposit s.TermDeposit2MaturityDate,Termdeposits.TermDeposi t3MaturityDate"

strWhere = strSelect & " Between " &
Format(Me.txtStartDate, conDateFormat) & " And " &
Format(Me.txtEndDate, conDateFormat)

' Debug.Print strWhere 'For debugging
purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Try this line:

strWhere = "(TermDeposit1MaturityDate Between " & Format(Me.txtStartDate,
conDateFormat) & " And " & Format(Me.txtEndDate, conDateFormat) & ") OR
(TermDeposit2MaturityDate Between " & Format(Me.txtStartDate, conDateFormat)
& " And " & Format(Me.txtEndDate, conDateFormat) & ") OR
(TermDeposit3MaturityDate Between " & Format(Me.txtStartDate, conDateFormat)
& " And " & Format(Me.txtEndDate, conDateFormat) & ")"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"QBCM" <le*****@qbcm.com-dot-au.no-spam.invalid> wrote in message
news:l8********************@giganews.com...
I am trying to create a report by selecting three date fields from one
table with records between a start date and end date. I have tried to
adapt one of Allen Browne's scripts as follows but it gives me records
outside my date criteria - I guess my strSelect is wrong! I tried
strSelect = * but that didn't work either.

Private Sub CmndOK_Click()
Dim strReport As String 'Name of report to open.
Dim strWhere As String 'Where condition for OpenReport.
Dim strSelect As String
Dim strFrom As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "TermDeposits"

strSelect =
"Termdeposits.TermDeposit1MaturityDate,Termdeposit s.TermDeposit2MaturityDate,Termdeposits.TermDeposi t3MaturityDate"

strWhere = strSelect & " Between " &
Format(Me.txtStartDate, conDateFormat) & " And " &
Format(Me.txtEndDate, conDateFormat)

' Debug.Print strWhere 'For debugging
purposes only.
DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub

Nov 13 '05 #2

P: n/a
That's probably an irritating enough problem to create a special
function to put all your dates in US Format... would be really nice if
Access would just apply the OS setting...

Nov 13 '05 #3

P: n/a
Hi Allen
Many thanks for replying I have laboured for some time on this! When
I try your WHERE clause I get an error - Compile error Expected: End
of statement - and it highlights the the first OR

Thanks for your assistance

Nov 13 '05 #4

P: n/a
To see an example of what you need to get, create a query into this table.

Drag the 3 date fields into the grid.
In the criteria below each, enter a date range, e.g.:
Between #1/1/2005# And #2/1/2005#
Be sure to use 3 different rows of the criteria.

Now switch to SQL View (View menu.)
Look at the WHERE clause in the query.
You need to create a string that ends up like that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"QBCM" <le*****@qbcm.com-dot-au.no-spam.invalid> wrote in message
news:ef********************@giganews.com...
Hi Allen
Many thanks for replying I have laboured for some time on this! When
I try your WHERE clause I get an error - Compile error Expected: End
of statement - and it highlights the the first OR

Thanks for your assistance

Nov 13 '05 #5

P: n/a
Hi Allen

It works fine now - must have been a typo on my part!

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.