Data Type mismatch

[Alain]

Hi,

I need to know why I get this data type mismatch error in the following:
strPays = rst![CountryCode]
Set rst = Nothing

'find latest number
strNum = NZ(DMax("Right$([BranchNo], 2)", "Branch", "Left$([BranchNo],2)
LIKE '" & strPays & "'"),0)

The field BranchNo is a text field like CA-01, US-17 ect....
What I need to to is to retreive the highest number so I can create a new one

Thanks

 

[Jeff Boyce]

Have you tried using that DMax() expression in a query to see what you get?

By the way, if you use the "Like" clause but don't use any wildcard symbols,
it's equivalent to not using the Like clause!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

Assuming that the line assigning a value to strNum is generating the error.

What is strNum declared as? Since you are assigning zero to it with the NZ
function I would think it needs to be a number. OH, your DMax is returning a
STRING so it needs to be a string? So, hopefully strNum is a variant.

Also Right$ and Left$ cannot process NULLS so that could be a problem.

strNum = NZ(DMax("Right([BranchNo], 2)", "Branch", "[BranchNo] Like'" &
strPays & "*'"),"00")

If that fails try breaking this down into steps to trouble shoot it.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Alain]

strNum is declare as string, not the problem
the problem really comes from the criteria itself
"Left$([BranchNo],2) LIKE '" & strPays & "'"

I have tried also "Left$([BranchNo],2) = 'CA'" before replacing it with a
variable and still no success

Assuming that the line assigning a value to strNum is generating the error.

What is strNum declared as? Since you are assigning zero to it with the NZ
function I would think it needs to be a number. OH, your DMax is returning a
STRING so it needs to be a string? So, hopefully strNum is a variant.

Also Right$ and Left$ cannot process NULLS so that could be a problem.

strNum = NZ(DMax("Right([BranchNo], 2)", "Branch", "[BranchNo] Like'" &
strPays & "*'"),"00")

If that fails try breaking this down into steps to trouble shoot it.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I need to know why I get this data type mismatch error in the following:
strPays = rst![CountryCode]
Set rst = Nothing

'find latest number
strNum = NZ(DMax("Right$([BranchNo], 2)", "Branch", "Left$([BranchNo],2)
LIKE '" & strPays & "'"),0)

The field BranchNo is a text field like CA-01, US-17 ect....
What I need to to is to retreive the highest number so I can create a new one

Thanks

.

 

[Alain]

just found the solution, I remove the $ on the left function and that was it

Thanks

strNum is declare as string, not the problem
the problem really comes from the criteria itself
"Left$([BranchNo],2) LIKE '" & strPays & "'"

I have tried also "Left$([BranchNo],2) = 'CA'" before replacing it with a
variable and still no success

Assuming that the line assigning a value to strNum is generating the error.

What is strNum declared as? Since you are assigning zero to it with the NZ
function I would think it needs to be a number. OH, your DMax is returning a
STRING so it needs to be a string? So, hopefully strNum is a variant.

Also Right$ and Left$ cannot process NULLS so that could be a problem.

strNum = NZ(DMax("Right([BranchNo], 2)", "Branch", "[BranchNo] Like'" &
strPays & "*'"),"00")

If that fails try breaking this down into steps to trouble shoot it.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I need to know why I get this data type mismatch error in the following:
strPays = rst![CountryCode]
Set rst = Nothing

'find latest number
strNum = NZ(DMax("Right$([BranchNo], 2)", "Branch", "Left$([BranchNo],2)
LIKE '" & strPays & "'"),0)

The field BranchNo is a text field like CA-01, US-17 ect....
What I need to to is to retreive the highest number so I can create a new one

Thanks

.