Correct syntax for numeric and date expression.

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

Guest

The below setups a filter from user input on a form. Customer Number and
Expiration Date.


Dim stCustomerNo As String
Dim stLinkCriteria As Variant
strExpireDate = "#" & ExpireDate & "#"

stCustomerNo = Forms!frmPermitRenewals!CustomerNo

stLinkCriteria = "[CustomerNo]=" & stCustomerNo And
"PermitExpireDate = " & strExpireDate
Me.Filter = stLinkCriteria

I am receiving a "Run-time error 13 - Type Mismatch

What is the correct sytax for this expression
 
Try:
Dim stLinkCriteria As Variant
If IsNull(Me.ExpireDate) Or IsNull(Forms!frmPermitRenewals!CustomerNo)
Then
Beep
Else
stLinkCriteria - "(CustomerNo = " & _
Forms!frmPermitRenewals!CustomerNo & _
") AND (PermitExpireDate = #" & _
Format(Me.ExpireDate, "mm/dd/yyyy") & "#)"
Debug.Print stLinkCriteria
Me.Filter = stLinkCriteria
Me.FilterOn = True
End If

If the CustomerNo field is Text, you will need extra quotes.

If it still fails, open the Immediate Window (Ctrl+G). The Debug.Print line
will show you the string that failed. It may help you understand why it did
not work as a filter. (The string needs to look exactly like the WHERE
clause of a query.)
 
Thank you for your assistance.

I made some modification to your code and this works
stLinkCriteria = "(CustomerNo = " & stCustomerNo & ") AND
(PermitExpireDate = #" & Format(strExpireDate, "mm/dd/yyyy") & "#)"


Allen Browne said:
Try:
Dim stLinkCriteria As Variant
If IsNull(Me.ExpireDate) Or IsNull(Forms!frmPermitRenewals!CustomerNo)
Then
Beep
Else
stLinkCriteria - "(CustomerNo = " & _
Forms!frmPermitRenewals!CustomerNo & _
") AND (PermitExpireDate = #" & _
Format(Me.ExpireDate, "mm/dd/yyyy") & "#)"
Debug.Print stLinkCriteria
Me.Filter = stLinkCriteria
Me.FilterOn = True
End If

If the CustomerNo field is Text, you will need extra quotes.

If it still fails, open the Immediate Window (Ctrl+G). The Debug.Print line
will show you the string that failed. It may help you understand why it did
not work as a filter. (The string needs to look exactly like the WHERE
clause of a query.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

iholder said:
The below setups a filter from user input on a form. Customer Number and
Expiration Date.


Dim stCustomerNo As String
Dim stLinkCriteria As Variant
strExpireDate = "#" & ExpireDate & "#"

stCustomerNo = Forms!frmPermitRenewals!CustomerNo

stLinkCriteria = "[CustomerNo]=" & stCustomerNo And
"PermitExpireDate = " & strExpireDate
Me.Filter = stLinkCriteria

I am receiving a "Run-time error 13 - Type Mismatch

What is the correct sytax for this expression
Click to expand...
Click to expand...
 
Back
Top