Select multiple date fields from one table

[QBCM]

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

[Allen Browne]

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

[pietlinden]

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...

[QBCM]

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

[Allen Browne]

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

[QBCM]

Hi Allen

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