Help! A complicate VBA inquiry, difficult in STRING treatment

[Martin \(Martin Lee\)]

The following VBA is wrong, how to make it correct :

[Forms]![mydatafileform].Filter = "bstatus like '" & Me![Combo156] & "'
AND "" & Me![Combo158] &"" Between " & _
Format(Me.Text149, "\#yyyy-mm-dd\#") & " AND " & _
Format(Me.Text151, "\#yyyy-mm-dd\#")

My explain:

this VBA inquiry have 3 criterias:
1. bstatus should = COMBO156

2. For example, if combo158's content is APPLICATION DATE , then
criteria is: [application date] is between 2006-01-20 to 2006-02-20 ( these
two days is for example to make it easier for you to understand)
if combo156's content is FININSHED DATE, then criteria
is : [FINISHED DATE] is between 2006-01-20 to 2006-02-20
REMARK: [application date] and [FINISHED DATE] are both field names.

3. the upper "2006-01-20" to "2006-02-20" is input by the user, in
TEXT149 and TEXT151.

 

[Guest]

Here's a hint on how to debug this kind of problem

instead of writing ...

[Forms]![mydatafileform].Filter = "bstatus like '" & Me![Combo156] & "'
AND "" & Me![Combo158] &"" Between " & _
Format(Me.Text149, "\#yyyy-mm-dd\#") & " AND " & _
Format(Me.Text151, "\#yyyy-mm-dd\#")

write
dim sSql as string
sSql = "bstatus like '" & Me![Combo156] & "'
AND "" & Me![Combo158] &"" Between " & _
Format(Me.Text149, "\#yyyy-mm-dd\#") & " AND " & _
Format(Me.Text151, "\#yyyy-mm-dd\#")

Then put a break point on this statement.
when the execution stops on the breakpoint
open an "Immediate" window and type
?sSql
You will probably find that you need some [] brackets

 

[Martin \(Martin Lee\)]

You are my hero! Thank you very much!

Martin Lee

Here's a hint on how to debug this kind of problem

instead of writing ...

[Forms]![mydatafileform].Filter = "bstatus like '" & Me![Combo156] & "'
AND "" & Me![Combo158] &"" Between " & _
Format(Me.Text149, "\#yyyy-mm-dd\#") & " AND " & _
Format(Me.Text151, "\#yyyy-mm-dd\#")

write
dim sSql as string
sSql = "bstatus like '" & Me![Combo156] & "'
AND "" & Me![Combo158] &"" Between " & _
Format(Me.Text149, "\#yyyy-mm-dd\#") & " AND " & _
Format(Me.Text151, "\#yyyy-mm-dd\#")

Then put a break point on this statement.
when the execution stops on the breakpoint
open an "Immediate" window and type
?sSql
You will probably find that you need some [] brackets

The following VBA is wrong, how to make it correct :

[Forms]![mydatafileform].Filter = "bstatus like '" & Me![Combo156] &
"'
AND "" & Me![Combo158] &"" Between " & _
Format(Me.Text149, "\#yyyy-mm-dd\#") & " AND " & _
Format(Me.Text151, "\#yyyy-mm-dd\#")

My explain:

this VBA inquiry have 3 criterias:
1. bstatus should = COMBO156

2. For example, if combo158's content is APPLICATION DATE , then
criteria is: [application date] is between 2006-01-20 to 2006-02-20 (
these
two days is for example to make it easier for you to understand)
if combo156's content is FININSHED DATE, then
criteria
is : [FINISHED DATE] is between 2006-01-20 to 2006-02-20
REMARK: [application date] and [FINISHED DATE] are both field names.

3. the upper "2006-01-20" to "2006-02-20" is input by the user, in
TEXT149 and TEXT151.