Type doesnt match

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hi,

At this point i give up. Take a look at the following code:
-------------------------
Private Sub dn_postcode_LostFocus()
Dim TmpPlaats As String
Dim TmpPostcode As Long

TmpPostcode = CLng(Left(Me.dn_postcode, 4))

If IsNull(Me.dn_woonplaats) Then

TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=" &
[TmpPostcode] And "[POSTC_END] <=" & [TmpPostcode])
MsgBox (TmpPlaats)

End If
End Sub
-------------------------

When using the above code i get a "type doesnt match" error.

If i use: TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG]
=" & [TmpPostcode])
Click to expand...

I dont get an error!!

POSTC_BEG and POSTC_END are both defined as long
But using them together %$&@^%@$ ARGHHHHH!!

PLease help?

txs in advance,
Michael
 
Michael said:
At this point i give up. Take a look at the following code:
-------------------------
Private Sub dn_postcode_LostFocus()
Dim TmpPlaats As String
Dim TmpPostcode As Long

TmpPostcode = CLng(Left(Me.dn_postcode, 4))

If IsNull(Me.dn_woonplaats) Then

TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=" &
[TmpPostcode] And "[POSTC_END] <=" & [TmpPostcode])
MsgBox (TmpPlaats)

End If
End Sub
-------------------------

When using the above code i get a "type doesnt match" error.

If i use: TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG]
=" & [TmpPostcode])
Click to expand...

I dont get an error!!

POSTC_BEG and POSTC_END are both defined as long
But using them together %$&@^%@$ ARGHHHHH!!
Click to expand...


It sounds like qryPostcode does something to make the
POSTC_END field type text. Is that a calculated field?
 
I think you misplaced some quotes:

TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=" &
[TmpPostcode] & " And [POSTC_END] <=" & [TmpPostcode])

Also, conversion of TmpPostcode to Long seems unnecessary because in the
end TmpPostcode is converted back to a string for concatination.
Hope this helps,
Pavel
Hi,

At this point i give up. Take a look at the following code:
-------------------------
Private Sub dn_postcode_LostFocus()
Dim TmpPlaats As String
Dim TmpPostcode As Long

TmpPostcode = CLng(Left(Me.dn_postcode, 4))

If IsNull(Me.dn_woonplaats) Then

TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=" &
[TmpPostcode] And "[POSTC_END] <=" & [TmpPostcode])
MsgBox (TmpPlaats)

End If
End Sub
-------------------------

When using the above code i get a "type doesnt match" error.

If i use: TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG]
=" & [TmpPostcode])
Click to expand...

I dont get an error!!

POSTC_BEG and POSTC_END are both defined as long
But using them together %$&@^%@$ ARGHHHHH!!

PLease help?

txs in advance,
Michael
Click to expand...
 
Here is what you have if TmpPostCode = 9999

TmpPlaats = DLookup(field,table, "[POSTC_BEG] >= 9999" AND "[POSTC_END] <=
9999") so your condition becomes a boolean instead of a string.
I think you just need to move the quote to in front of the AND and add the
concantenation.

TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=" &
[TmpPostcode] & "And [POSTC_END] <=" & [TmpPostcode])

If [TmpPostCode] is a string, you will need to add quotes around it also.

Bob

Michael said:
Hi,

At this point i give up. Take a look at the following code:
-------------------------
Private Sub dn_postcode_LostFocus()
Dim TmpPlaats As String
Dim TmpPostcode As Long

TmpPostcode = CLng(Left(Me.dn_postcode, 4))

If IsNull(Me.dn_woonplaats) Then

TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=" &
[TmpPostcode] And "[POSTC_END] <=" & [TmpPostcode])
MsgBox (TmpPlaats)

End If
End Sub
-------------------------

When using the above code i get a "type doesnt match" error.

If i use: TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG]
=" & [TmpPostcode])
Click to expand...

I dont get an error!!

POSTC_BEG and POSTC_END are both defined as long
But using them together %$&@^%@$ ARGHHHHH!!

PLease help?

txs in advance,
Michael
Click to expand...
 
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=" &
[TmpPostcode] And "[POSTC_END] <=" & [TmpPostcode])
MsgBox (TmpPlaats)

End If
End Sub
-------------------------
Click to expand...

You need to put the word AND *inside* the quotes. What you're doing
with the concatenation is creating a valid SQL WHERE clause without
the word WHERE; and the word AND needs to be part of that string:

...."[POSTC_BEG] >=" & [tmpPostCode] & " AND [POSTC_END] <= " &
[tmpPostcode])

Thus if tmpPostcode contains 123, you'll get

[POSTC_BEG] >= 123 AND [POSTC_END] <= 123

Note that the blank before the word AND is essential too, otherwise
the string would contain 123AND - and would cause a different error.
POSTC_BEG and POSTC_END are both defined as long
But using them together %$&@^%@$ ARGHHHHH!!
Click to expand...

If these are actually Postcodes, I'd be strongly inclined to use a
Text datatype rather than a Number. If you do so you'll need the
syntactially required quotemarks in the criteria:

...."[POSTC_BEG] >='" & [tmpPostCode] & "' AND [POSTC_END] <= '" &
[tmpPostcode] & "'")
 
Txs for the suggestions guys but...

If I use:
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=" &
[TmpPostcode] & " And [POSTC_END] <=" & [TmpPostcode])

I get an error 94 "Invalid use of Null"

I looked at the query and does nothing with the fields POSTC_BEG and
POSTC_END. There both long.

Suggestions appriciated here

Michael




Michael said:
Hi,

At this point i give up. Take a look at the following code:
-------------------------
Private Sub dn_postcode_LostFocus()
Dim TmpPlaats As String
Dim TmpPostcode As Long

TmpPostcode = CLng(Left(Me.dn_postcode, 4))

If IsNull(Me.dn_woonplaats) Then

TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=" &
[TmpPostcode] And "[POSTC_END] <=" & [TmpPostcode])
MsgBox (TmpPlaats)

End If
End Sub
-------------------------

When using the above code i get a "type doesnt match" error.

If i use: TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG]
=" & [TmpPostcode])
Click to expand...

I dont get an error!!

POSTC_BEG and POSTC_END are both defined as long
But using them together %$&@^%@$ ARGHHHHH!!

PLease help?

txs in advance,
Michael
Click to expand...
 
If I use
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >= 5000" & "
And [POSTC_END] <= 5000")

Thus replacing the variable with a fixed value i still get the error 94
"Ïnvalid use of Null"
So that doesnt seem to be the problem

if i use the conditions 1 at a time:
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_END] <= 5000")
or
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >= 5000")
I get a valid sesponse

ARGH!!

Michael
Michael said:
Hi,

At this point i give up. Take a look at the following code:
-------------------------
Private Sub dn_postcode_LostFocus()
Dim TmpPlaats As String
Dim TmpPostcode As Long

TmpPostcode = CLng(Left(Me.dn_postcode, 4))

If IsNull(Me.dn_woonplaats) Then

TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=" &
[TmpPostcode] And "[POSTC_END] <=" & [TmpPostcode])
MsgBox (TmpPlaats)

End If
End Sub
-------------------------

When using the above code i get a "type doesnt match" error.

If i use: TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG]
=" & [TmpPostcode])
Click to expand...

I dont get an error!!

POSTC_BEG and POSTC_END are both defined as long
But using them together %$&@^%@$ ARGHHHHH!!

PLease help?

txs in advance,
Michael
Click to expand...
 
That implies that there isn't any value of plaatsnaam for which POSTC_END is
<= 5000 and POSTC_BEG >= 5000. In other words, DLookup is returning a Null,
and you can't assign a Null to a string variable.

Try

TmpPlaats = Nz(DLookup("[plaatsnaam]", "qryPostcode", _
"[POSTC_BEG] >=" & [TmpPostcode] & _
" And [POSTC_END] <= " & [TmpPostcode]), _
"***No Record Found***")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Michael said:
If I use
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >= 5000" & "
And [POSTC_END] <= 5000")

Thus replacing the variable with a fixed value i still get the error 94
"Ïnvalid use of Null"
So that doesnt seem to be the problem

if i use the conditions 1 at a time:
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_END] <= 5000")
or
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >= 5000")
I get a valid sesponse

ARGH!!

Michael
Michael said:
Hi,

At this point i give up. Take a look at the following code:
-------------------------
Private Sub dn_postcode_LostFocus()
Dim TmpPlaats As String
Dim TmpPostcode As Long

TmpPostcode = CLng(Left(Me.dn_postcode, 4))

If IsNull(Me.dn_woonplaats) Then

TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=" &
[TmpPostcode] And "[POSTC_END] <=" & [TmpPostcode])
MsgBox (TmpPlaats)

End If
End Sub
-------------------------

When using the above code i get a "type doesnt match" error.

If i use: TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG]
=" & [TmpPostcode])
Click to expand...

I dont get an error!!

POSTC_BEG and POSTC_END are both defined as long
But using them together %$&@^%@$ ARGHHHHH!!

PLease help?

txs in advance,
Michael
Click to expand...
Click to expand...
 
Well Doug,

Youre right about the TmpPlaats but i dont understand why its not finding
it.

The query has 3 columns :
1e = WOONPLAATS (string)
2nd = POSTC_BEG (long)
3rd = POSTC_END (long)

ie:

Tilburg, 5000, 5049
Goirle, 5050, 5052
Berkel-Enschot, 5056, 5056
Oisterwijk, 5060, 5062
etc....

I am still missing something here??

Michael

Douglas J. Steele said:
That implies that there isn't any value of plaatsnaam for which POSTC_END is
<= 5000 and POSTC_BEG >= 5000. In other words, DLookup is returning a Null,
and you can't assign a Null to a string variable.

Try

TmpPlaats = Nz(DLookup("[plaatsnaam]", "qryPostcode", _
"[POSTC_BEG] >=" & [TmpPostcode] & _
" And [POSTC_END] <= " & [TmpPostcode]), _
"***No Record Found***")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Michael said:
If I use
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >= 5000"
Click to expand...
&
"
And [POSTC_END] <= 5000")

Thus replacing the variable with a fixed value i still get the error 94
"Ïnvalid use of Null"
So that doesnt seem to be the problem

if i use the conditions 1 at a time:
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_END] <= 5000")
or
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >= 5000")
I get a valid sesponse

ARGH!!

Michael
Michael said:
Hi,

At this point i give up. Take a look at the following code:
-------------------------
Private Sub dn_postcode_LostFocus()
Dim TmpPlaats As String
Dim TmpPostcode As Long

TmpPostcode = CLng(Left(Me.dn_postcode, 4))

If IsNull(Me.dn_woonplaats) Then

TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG]
Click to expand...
="
&
[TmpPostcode] And "[POSTC_END] <=" & [TmpPostcode])
MsgBox (TmpPlaats)

End If
End Sub
-------------------------

When using the above code i get a "type doesnt match" error.

If i use: TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG]
=" & [TmpPostcode])

I dont get an error!!

POSTC_BEG and POSTC_END are both defined as long
But using them together %$&@^%@$ ARGHHHHH!!

PLease help?

txs in advance,
Michael
Click to expand...
Click to expand...
Click to expand...
 
Now that I can see your data, your inequalities are wrong.

For a given TmpPostcode, you want the one where POSTC_BEG <= your input and
POSTC_END >= your input. You've got the opposite.

Change to:

TmpPlaats = Nz(DLookup("[plaatsnaam]", "qryPostcode", _
"[POSTC_BEG] <=" & [TmpPostcode] & _
" And [POSTC_END] >= " & [TmpPostcode]), _
"***No Record Found***")

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Michael said:
Well Doug,

Youre right about the TmpPlaats but i dont understand why its not finding
it.

The query has 3 columns :
1e = WOONPLAATS (string)
2nd = POSTC_BEG (long)
3rd = POSTC_END (long)

ie:

Tilburg, 5000, 5049
Goirle, 5050, 5052
Berkel-Enschot, 5056, 5056
Oisterwijk, 5060, 5062
etc....

I am still missing something here??

Michael

Douglas J. Steele said:
That implies that there isn't any value of plaatsnaam for which
Click to expand...
POSTC_END
is
<= 5000 and POSTC_BEG >= 5000. In other words, DLookup is returning a Null,
and you can't assign a Null to a string variable.

Try

TmpPlaats = Nz(DLookup("[plaatsnaam]", "qryPostcode", _
"[POSTC_BEG] >=" & [TmpPostcode] & _
" And [POSTC_END] <= " & [TmpPostcode]), _
"***No Record Found***")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Michael said:
If I use
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=
Click to expand...
Click to expand...
5000"
&
"
And [POSTC_END] <= 5000")

Thus replacing the variable with a fixed value i still get the error 94
"Ïnvalid use of Null"
So that doesnt seem to be the problem

if i use the conditions 1 at a time:
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_END] <= 5000")
or
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >= 5000")
I get a valid sesponse

ARGH!!

Michael
"Michael" <[email protected]> schreef in bericht
Hi,

At this point i give up. Take a look at the following code:
-------------------------
Private Sub dn_postcode_LostFocus()
Dim TmpPlaats As String
Dim TmpPostcode As Long

TmpPostcode = CLng(Left(Me.dn_postcode, 4))

If IsNull(Me.dn_woonplaats) Then

TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] ="
&
[TmpPostcode] And "[POSTC_END] <=" & [TmpPostcode])
MsgBox (TmpPlaats)

End If
End Sub
-------------------------

When using the above code i get a "type doesnt match" error.

If i use: TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG]
=" & [TmpPostcode])

I dont get an error!!

POSTC_BEG and POSTC_END are both defined as long
But using them together %$&@^%@$ ARGHHHHH!!

PLease help?

txs in advance,
Michael
Click to expand...
Click to expand...
Click to expand...
 
AARRRGHHHH!!!

I feel very small atm! :-)

Txs a lot

Michael

Douglas J. Steele said:
Now that I can see your data, your inequalities are wrong.

For a given TmpPostcode, you want the one where POSTC_BEG <= your input and
POSTC_END >= your input. You've got the opposite.

Change to:

TmpPlaats = Nz(DLookup("[plaatsnaam]", "qryPostcode", _
"[POSTC_BEG] <=" & [TmpPostcode] & _
" And [POSTC_END] >= " & [TmpPostcode]), _
"***No Record Found***")

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Michael said:
Well Doug,

Youre right about the TmpPlaats but i dont understand why its not finding
it.

The query has 3 columns :
1e = WOONPLAATS (string)
2nd = POSTC_BEG (long)
3rd = POSTC_END (long)

ie:

Tilburg, 5000, 5049
Goirle, 5050, 5052
Berkel-Enschot, 5056, 5056
Oisterwijk, 5060, 5062
etc....

I am still missing something here??

Michael

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> schreef in bericht
That implies that there isn't any value of plaatsnaam for which
Click to expand...
POSTC_END
is
<= 5000 and POSTC_BEG >= 5000. In other words, DLookup is returning a Null,
and you can't assign a Null to a string variable.

Try

TmpPlaats = Nz(DLookup("[plaatsnaam]", "qryPostcode", _
"[POSTC_BEG] >=" & [TmpPostcode] & _
" And [POSTC_END] <= " & [TmpPostcode]), _
"***No Record Found***")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



If I use
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >=
Click to expand...
5000"
&
"
And [POSTC_END] <= 5000")

Thus replacing the variable with a fixed value i still get the error 94
"Ïnvalid use of Null"
So that doesnt seem to be the problem

if i use the conditions 1 at a time:
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_END] <= 5000")
or
TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG] >= 5000")
I get a valid sesponse

ARGH!!

Michael
"Michael" <[email protected]> schreef in bericht
Hi,

At this point i give up. Take a look at the following code:
-------------------------
Private Sub dn_postcode_LostFocus()
Dim TmpPlaats As String
Dim TmpPostcode As Long

TmpPostcode = CLng(Left(Me.dn_postcode, 4))

If IsNull(Me.dn_woonplaats) Then

TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode", "[POSTC_BEG]
="
&
[TmpPostcode] And "[POSTC_END] <=" & [TmpPostcode])
MsgBox (TmpPlaats)

End If
End Sub
-------------------------

When using the above code i get a "type doesnt match" error.

If i use: TmpPlaats = DLookup("[plaatsnaam]", "qryPostcode",
"[POSTC_BEG]
=" & [TmpPostcode])

I dont get an error!!

POSTC_BEG and POSTC_END are both defined as long
But using them together %$&@^%@$ ARGHHHHH!!

PLease help?

txs in advance,
Michael
Click to expand...
Click to expand...
Click to expand...
 
It happens to all of us!

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Michael said:
AARRRGHHHH!!!

I feel very small atm! :-)

Txs a lot

Michael

Douglas J. Steele said:
Now that I can see your data, your inequalities are wrong.

For a given TmpPostcode, you want the one where POSTC_BEG <= your input and
POSTC_END >= your input. You've got the opposite.

Change to:

TmpPlaats = Nz(DLookup("[plaatsnaam]", "qryPostcode", _
"[POSTC_BEG] <=" & [TmpPostcode] & _
" And [POSTC_END] >= " & [TmpPostcode]), _
"***No Record Found***")
Click to expand...
Click to expand...
 
Back
Top