combining report filter

  • Thread starter Thread starter Song Su
  • Start date Start date
S

Song Su

My report nees following fileter: (all 3 fields are text type)

[GL] = cboGL AND [fund] Between cboFundFm AND cboFundTo

I did the following. But I dont' know how to add rest:

DoCmd.OpenReport "rptFundByGL", acViewPreview, , "[GL] = '" & cboGL & "'"
 
DoCmd.OpenReport "rptFundByGL", acViewPreview, , "[GL] = '" & Me.cboGL & "'
AND [fund] Between(#" & Me.cboFundFm & "AND #" & Me.cboFundTo & "#)"
 
your code generates syntax error. my fields are text type. don't know what's
wrong.

Klatuu said:
DoCmd.OpenReport "rptFundByGL", acViewPreview, , "[GL] = '" & Me.cboGL &
"'
AND [fund] Between(#" & Me.cboFundFm & "AND #" & Me.cboFundTo & "#)"
--
Dave Hargis, Microsoft Access MVP


Song Su said:
My report nees following fileter: (all 3 fields are text type)

[GL] = cboGL AND [fund] Between cboFundFm AND cboFundTo

I did the following. But I dont' know how to add rest:

DoCmd.OpenReport "rptFundByGL", acViewPreview, , "[GL] = '" & cboGL & "'"
Click to expand...
Click to expand...
 
your code generates syntax error. my fields are text type. don't know what's
wrong.
Click to expand...

I think Dave assumed that the BETWEEN criteria were for a date rather than a
text string. Use ' as a delimiter instead:

DoCmd.OpenReport "rptFundByGL", acViewPreview, , "[GL] = '" & Me.cboGL & "'
AND [fund] Between '" & Me.cboFundFm & "' AND '" & Me.cboFundTo & "')"

For readability (don't actually do it this way!) that's

DoCmd.OpenReport "rptFundByGL", acViewPreview, , "[GL] = ' " & Me.cboGL & " '
AND [fund] Between ' " & Me.cboFundFm & " ' AND ' " & Me.cboFundTo & " ')"

This will produce a string like

[GL] = 'xyz' AND [Fund] Between 'abc' AND 'def'

which I hope is what you intend (to get Fund values within an alphabetical
range).
 
Thanks, John.
Rereading, I see she said text and now I remember reading that. But, when I
was writing it, I saw a field with date in it and, silly me, made an
assumption it was a date field.

Now, since it is a text field carrying a date, depending on how the date is
styled, it may or may not return the correct results.
--
Dave Hargis, Microsoft Access MVP


John W. Vinson said:
your code generates syntax error. my fields are text type. don't know what's
wrong.
Click to expand...

I think Dave assumed that the BETWEEN criteria were for a date rather than a
text string. Use ' as a delimiter instead:

DoCmd.OpenReport "rptFundByGL", acViewPreview, , "[GL] = '" & Me.cboGL & "'
AND [fund] Between '" & Me.cboFundFm & "' AND '" & Me.cboFundTo & "')"

For readability (don't actually do it this way!) that's

DoCmd.OpenReport "rptFundByGL", acViewPreview, , "[GL] = ' " & Me.cboGL & " '
AND [fund] Between ' " & Me.cboFundFm & " ' AND ' " & Me.cboFundTo & " ')"

This will produce a string like

[GL] = 'xyz' AND [Fund] Between 'abc' AND 'def'

which I hope is what you intend (to get Fund values within an alphabetical
range).
Click to expand...
 
Rereading, I see she said text and now I remember reading that. But, when I
was writing it, I saw a field with date in it and, silly me, made an
assumption it was a date field.
Click to expand...

Just what I figured, Dave - until she posted back I was making the same
assumption.
Now, since it is a text field carrying a date, depending on how the date is
styled, it may or may not return the correct results.
Click to expand...

Yep. The text string "01-15-2008" is in fact BETWEEN "01-01-2005" and
"12-31-2005" even though the date isn't.
 
John,

I don't understand why you have a ) at end while there is no opening (?

John W. Vinson said:
your code generates syntax error. my fields are text type. don't know
what's
wrong.
Click to expand...

I think Dave assumed that the BETWEEN criteria were for a date rather than
a
text string. Use ' as a delimiter instead:

DoCmd.OpenReport "rptFundByGL", acViewPreview, , "[GL] = '" & Me.cboGL &
"'
AND [fund] Between '" & Me.cboFundFm & "' AND '" & Me.cboFundTo & "')"

For readability (don't actually do it this way!) that's

DoCmd.OpenReport "rptFundByGL", acViewPreview, , "[GL] = ' " & Me.cboGL &
" '
AND [fund] Between ' " & Me.cboFundFm & " ' AND ' " & Me.cboFundTo & "
')"

This will produce a string like

[GL] = 'xyz' AND [Fund] Between 'abc' AND 'def'

which I hope is what you intend (to get Fund values within an alphabetical
range).
Click to expand...
 
I don't understand why you have a ) at end while there is no opening (?
Click to expand...

Because I goofed <blush>

Just take it out. I think it was left in from a previous version which did
have parentheses.
 
Back
Top