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

Need help with multi syntax

P: n/a
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

Jul 18 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Jul 19 '07 #2

P: n/a
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

Jul 19 '07 #3

P: n/a
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 <rbrown...@compuserve.comwrote:
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

Jul 19 '07 #4

P: n/a
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" <robemce...@hotmail.comwrote:
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.spam.tom7...@cox.netwrote in messagenews:fk********************************@4ax .com...
On Wed, 18 Jul 2007 13:31:37 -0700, 2D Rick <rbrown...@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- Hide quoted text -

- Show quoted text -

Jul 19 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.