Type Mismatch

[tonik]

Any genius’ out there that can help me with this one?

I'm using Access 2002 on WinXP.

What I have is a document ID number system. The ID can be formatted in the following ways:

000.000
000.000.00
000.000.0000

What I’m running into is when the DLookup finds a match that is greater than 7 characters I get a type mismatch error. I thought that it may be looking at it as a number, so I’ve added a line of code that converts it back into a string if that was the case. I still get the error either way. Here’s the code:

Private Sub cmbCatNo_AfterUpdate()
On Error GoTo Err_cmbCatNo_AfterUpdate

Dim strCatNo As String
Dim strSecNo As String
Dim strSeqNo As String
Dim dblConvertNo As Double

'Three digit category number pulled from a control on the form.
strCatNo = [Forms]![frmDocumentNamingForm]![cmbCatNo]
'First number in sequence for the secondary number control.
strSecNo = ".001"
'First concantinated sequence number to compare.
strSeqNo = "" & strCatNo & strSecNo & ""

'Clears secondary number field on form.
[Forms]![frmDocumentNamingForm]![txtSecNo] = ""

'Looks for a match in database to the latest sequenced number. FileID is a string datatype.
Do While DLookup("[FileID]", "DocumentMaster", "[FileID] Like '" & strSeqNo & "*'")

'Converts the string into a decimal and add the next sequenced number.
dblConvertNo = CDbl(strSeqNo) + 0.001
'Reformats the number for comparison.
strSeqNo = Format(dblConvertNo, "000.000")
'Converts the data back into a text string.
strSeqNo = "" & CStr(strSeqNo) & ""
MsgBox strSeqNo

Loop

'If no match is found, it inputs the right three digits of the sequence number into the secondary number control.
[Forms]![frmDocumentNamingForm]![txtSecNo] = Right(strSeqNo, 3)

Exit_cmbCatNo_AfterUpdate:
Exit Sub

Err_cmbCatNo_AfterUpdate:
MsgBox Error$
Resume Exit_cmbCatNo_AfterUpdate

End Sub

[tonik]

Bump...Any takers that can bless me with their ultimate knowledge?

[ECA]

"[FileID] Like '" & strSeqNo & "*'")

Extra charactor in string def... LOOK CLOSE...

" ' " AFTER "LIKE" and AFTER " * "

[tonik]

Ok, I had taken the two single quotes out so it looks like this:

Do While DLookup("[FileID]", "DocumentMaster", "[FileID] Like " & strSeqNo & "*")

Then ran the code and got this error:

Syntax error (missing operator) in query expression '[FileID] Like 010.001*'.

[seans]

I've got no help for you but I do remember now why I vowed never to look at VB again and stay where I belong with C and it's randy pack of offspring.

okok I do have something for you, "type mismatch" means that either access or VB does not like the format in which you're number is. I'm fairly sure that neither Access or VB will accept a number with two decimal points since, well that would make it a fraction of a franction and that can't be right can it? So more than likely the field in the database you're working with and this string because that's what it is are mismatched. You'll need to make sure that the field is the same type?

Its a shot in the dark, but what do I know I'm just an open source guy.

edit// another thought... why not use a GUID for the doc versions instead of creating your own shorter version? All that is built in....

[ECA]

I aint been a programmer for along time...
What is this for, and WHY are you useing 3 sections..000.000.00???
Cant you read the WHOLE name in as a STRING??
Then count the charactors?
then if needed, take the last of the string, and convert it to a Number value, and check it? Then ADD whats needed, then SAVE as a string/name..