Need help with multi syntax

[2D Rick]

Dim dtDay1 As Date
Dim dtDay2 As Date
Dim dtDay3 As Date

'once working I'll load these variables from a multi listbox.
dtDay1 = #7/22/2007#
dtDay2 = #7/24/2007#
dtDay3 = #7/25/2007#
This works:
DoCmd.OpenReport "frmTest", acViewPreview, , "[Weekend_Date] = " & "#"
& dtDay1 & "#"

This does not work:
DoCmd.OpenReport "frmTest", acViewPreview, , "[Weekend_Date] = " & "#"
& dtDay1 & "#" Or "[Weekend_Date] = " & "#" & dtDay2 & "#" Or
"[Weekend_Date] = " & "#" & dtDay3 & "#"

I get a type mismatch error but I think its poor syntax in the Where
Clause. Probably the quotes againnnnnnnn.

RICK

[Tom van Stiphout]

On Wed, 18 Jul 2007 13:31:37 -0700, 2D Rick <rb*******@compuserve.com>
wrote:

The WHERE argument needs to be a single string.

-Tom.

>Dim dtDay1 As Date
Dim dtDay2 As Date
Dim dtDay3 As Date

'once working I'll load these variables from a multi listbox.
dtDay1 = #7/22/2007#
dtDay2 = #7/24/2007#
dtDay3 = #7/25/2007#
This works:
DoCmd.OpenReport "frmTest", acViewPreview, , "[Weekend_Date] = " & "#"
& dtDay1 & "#"

This does not work:
DoCmd.OpenReport "frmTest", acViewPreview, , "[Weekend_Date] = " & "#"
& dtDay1 & "#" Or "[Weekend_Date] = " & "#" & dtDay2 & "#" Or
"[Weekend_Date] = " & "#" & dtDay3 & "#"

I get a type mismatch error but I think its poor syntax in the Where
Clause. Probably the quotes againnnnnnnn.

RICK

[RMCEU1]

Simple answer: use IN in your criteria clause...it will look something like
this:

DoCmd.OpenReport "frmTest", acViewPreview, , "[Weekend_Date] IN (#" & dtDay1
& "#, #" & dtDay2 & "#,#" & dtDay3 & "#)"

But if you're using values from a listbox, you're probably wasting your time
trying to assign them to variables. What if the user only chooses one date?
Or chooses five? It's probably better to build the criteria clause
dynamically as you loop through the ItemsSelected collection of the list
box.

*air code*

strCriteria = "[Weekend_Date] IN (#"

With Me.lstMyDates
For each varItem in .ItemsSelected
strCriteria = strCriteria & CStr(.Column(0, varItem)) & "#,#"
' above assumes date is the bound listbox column - adjust as
necessary
Next varItem
End With

strCriteria = Left$(strCriteria, Len(strCriteria)-2)

DoCmd.OpenReport "frmTest", acViewPreview, , strCriteria
"Tom van Stiphout" <no*************@cox.netwrote in message
news:fk********************************@4ax.com...

On Wed, 18 Jul 2007 13:31:37 -0700, 2D Rick <rb*******@compuserve.com>
wrote:

The WHERE argument needs to be a single string.

-Tom.

>>Dim dtDay1 As Date
Dim dtDay2 As Date
Dim dtDay3 As Date

'once working I'll load these variables from a multi listbox.
dtDay1 = #7/22/2007#
dtDay2 = #7/24/2007#
dtDay3 = #7/25/2007#
This works:
DoCmd.OpenReport "frmTest", acViewPreview, , "[Weekend_Date] = " & "#"
& dtDay1 & "#"

This does not work:
DoCmd.OpenReport "frmTest", acViewPreview, , "[Weekend_Date] = " & "#"
& dtDay1 & "#" Or "[Weekend_Date] = " & "#" & dtDay2 & "#" Or
"[Weekend_Date] = " & "#" & dtDay3 & "#"

I get a type mismatch error but I think its poor syntax in the Where
Clause. Probably the quotes againnnnnnnn.

RICK

[King Ron]

You just got your quotes fuzzulated:

DoCmd.OpenReport "frmTest", acViewPreview, , "(([Weekend_Date] = #"
& dtDay1 & "#) OR ([Weekend_Date] = #" & dtDay2 & "#) OR
([Weekend_Date] = #" & dtDay3 & "#))"

remember that what you are doing is wrapping the text strings around
the values contained in your variables...

btw...I like parenthethezez...

Ron, King of Chi

On Jul 18, 3:31 pm, 2D Rick <[e-mail]:

Dim dtDay1 As Date
Dim dtDay2 As Date
Dim dtDay3 As Date

'once working I'll load these variables from a multi listbox.
dtDay1 = #7/22/2007#
dtDay2 = #7/24/2007#
dtDay3 = #7/25/2007#

This works:
DoCmd.OpenReport "frmTest", acViewPreview, , "[Weekend_Date] = " & "#"
& dtDay1 & "#"

This does not work:
DoCmd.OpenReport "frmTest", acViewPreview, , "[Weekend_Date] = " & "#"
& dtDay1 & "#" Or "[Weekend_Date] = " & "#" & dtDay2 & "#" Or
"[Weekend_Date] = " & "#" & dtDay3 & "#"

I get a type mismatch error but I think its poor syntax in the Where
Clause. Probably the quotes againnnnnnnn.

RICK

[bobh]

Great solution! I kept forgetting about the 'IN' and have any times
scratched my head thinking how am I going to do this...... :)
bobh.

On Jul 19, 1:42 am, "RMCEU1" <[e-mail]:

Simple answer: use IN in your criteria clause...it will look something like
this:

DoCmd.OpenReport "frmTest", acViewPreview, , "[Weekend_Date] IN (#" & dtDay1
& "#, #" & dtDay2 & "#,#" & dtDay3 & "#)"

But if you're using values from a listbox, you're probably wasting your time
trying to assign them to variables. What if the user only chooses one date?
Or chooses five? It's probably better to build the criteria clause
dynamically as you loop through the ItemsSelected collection of the list
box.

*air code*

strCriteria = "[Weekend_Date] IN (#"

With Me.lstMyDates
For each varItem in .ItemsSelected
strCriteria = strCriteria & CStr(.Column(0, varItem)) & "#,#"
' above assumes date is the bound listbox column - adjust as
necessary
Next varItem
End With

strCriteria = Left$(strCriteria, Len(strCriteria)-2)

DoCmd.OpenReport "frmTest", acViewPreview, , strCriteria

"Tom van Stiphout" <[e-mail] in messagenews:fk********************************@4ax .com...

On Wed, 18 Jul 2007 13:31:37 -0700, 2D Rick <[e-mail]>
wrote:

The WHERE argument needs to be a single string.

-Tom.

>Dim dtDay1 As Date
Dim dtDay2 As Date
Dim dtDay3 As Date

>'once working I'll load these variables from a multi listbox.
dtDay1 = #7/22/2007#
dtDay2 = #7/24/2007#
dtDay3 = #7/25/2007#

>This works:
DoCmd.OpenReport "frmTest", acViewPreview, , "[Weekend_Date] = " & "#"
& dtDay1 & "#"

>This does not work:
DoCmd.OpenReport "frmTest", acViewPreview, , "[Weekend_Date] = " & "#"
& dtDay1 & "#" Or "[Weekend_Date] = " & "#" & dtDay2 & "#" Or
"[Weekend_Date] = " & "#" & dtDay3 & "#"

>I get a type mismatch error but I think its poor syntax in the Where
Clause. Probably the quotes againnnnnnnn.

>RICK- Hide quoted text -

- Show quoted text -