DoCmd.OpenForm Syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to add an "And" in a where clause of the DoCmd.OpenForm (see
below), but I can't seem to get the syntax right re: the 'And'. I keep
getting a "Type Mismatch" error.

Does anyone know the right syntax?

Thanks

stDocName = "Applications"

stLinkCriteria = "[Date]=" & "#" & Me![Date] & "#" And "[StudentID]=" &
Me![StudentID]

DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Your StudentID is a string data type and thus needs to be enclosed in
quotes.

stLinkCriteria = "[Date]=" & "#" & Me![Date] & "#" And "[StudentID]= '" &
Me![StudentID] & "'"
 
Try this:
stLinkCriteria = "[Date] = #" & Me![Date] & "# And [StudentID] = " &
Me![StudentID]
 
I am trying to add an "And" in a where clause of the DoCmd.OpenForm (see
below), but I can't seem to get the syntax right re: the 'And'. I keep
getting a "Type Mismatch" error.

Does anyone know the right syntax?

Thanks

stDocName = "Applications"

stLinkCriteria = "[Date]=" & "#" & Me![Date] & "#" And "[StudentID]=" &
Me![StudentID]

DoCmd.OpenForm stDocName, , , stLinkCriteria
Click to expand...

A couple of mistakes.
1) Your AND is outside of the string

If StudentID is a Number datatype:
stLinkCriteria = "[DateField]= #" & Me![DateField] & "# And
[StudentID] = " & Me![StudentID]

If StudentID is Text datatype:
stLinkCriteria = "[DateField]= #" & Me![DateField] & "# And
[StudentID]= '" &
Me![StudentID] & "'"

2) Do you really have a field named "Date" in your database table?

Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
 
stLinkCriteria = "([Date] = #" & Me![Date] & "#) And ([StudentID] = " & _
Me![StudentID] & ")"

(assuming your Regional Settings for date is of format mm/dd/yyyy)

BTW, "Date" is not a good choice for a Field name since it is a reserved
word for the Date() function.
 
Back
Top