Docmd.Openform with where condition Syntax

[gavm360]

Hello, im trying to open a form from an dialog box form:

the button on the dialog box has this on the onclick event:
DoCmd.OpenForm "frmCASES_UNION", acViewNormal, , "MCH_CASECODE = #" &
Me!txtCCODE & "#"

the form I'm trying to open is "frmcases_union"
and im trying to filter it by the "MCH_CASECODE" which i type into
txtCCODE.

When i click i get:
Run-time error 3705
Syntax error in date in query expression MCH_CASECODE = #1360#

is it because im using # signs which makes it think its a date?? what
should i use as placeholders?

Thanks, Gavin

[fredg]

On Wed, 14 Nov 2007 22:09:46 -0000, ga*****@yahoo.com wrote:

Hello, im trying to open a form from an dialog box form:

the button on the dialog box has this on the onclick event:

DoCmd.OpenForm "frmCASES_UNION", acViewNormal, , "MCH_CASECODE = #" &
Me!txtCCODE & "#"

the form I'm trying to open is "frmcases_union"
and im trying to filter it by the "MCH_CASECODE" which i type into
txtCCODE.

When i click i get:
Run-time error 3705
Syntax error in date in query expression MCH_CASECODE = #1360#

is it because im using # signs which makes it think its a date?? what
should i use as placeholders?

Thanks, Gavin

Why are you using
"MCH_CASECODE = #" Me!txtCCODE & "#" ?
The # is a date delimiter symbol. By your surrounding your variable
with #, Access is expecting a date value, but MCH_CASECODE is not a
date field.

What is the datatype of the "MCH_CASECODE" field?

If MCH_CASECODE is Text datatype, then use:
"MCH_CASECODE ='" & Me!txtCCODE & "'"

The above will return the 1360 value as text:
"MCH_CASECODE = '1360'"

If it is a Number datatype then use:
"MCH_CASECODE =" & Me!txtCCODE

The above will return the 1360 value as a Number
"MCH_CASECODE = 1360"
I would suspect that you need the Number datatype syntax.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Allen Browne]

Yes, the # is used as delimiters around literal dates in the string.

If MCH_CASECODE is a Text field (when you open the field in design view),
use quote marks. Example:
Dim strWhere As String
If Not IsNull(Me.txtCCCODE) Then
strWhere = "MCH_CASECODE = """ & Me!txtCCODE & """"
End If
DoCmd.OpenForm "frmCASES_UNION", acViewNormal, , strWhere

By using a variable for the WhereCondition, you can add the line:
Debug.Print strWhere
and see what it prints to the Debug window if it fails.

For an explanation of the extra quotes, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

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

<ga*****@yahoo.comwrote in message
news:11**********************@22g2000hsm.googlegro ups.com...

Hello, im trying to open a form from an dialog box form:

the button on the dialog box has this on the onclick event:
DoCmd.OpenForm "frmCASES_UNION", acViewNormal, , "MCH_CASECODE = #" &
Me!txtCCODE & "#"

the form I'm trying to open is "frmcases_union"
and im trying to filter it by the "MCH_CASECODE" which i type into
txtCCODE.

When i click i get:
Run-time error 3705
Syntax error in date in query expression MCH_CASECODE = #1360#

is it because im using # signs which makes it think its a date?? what
should i use as placeholders?

Thanks, Gavin

[gavm360]

Thanks guys, that worked. I was incorrectly using the # placeholder
for a number datatype