Data type mismatch error

[systemx]

Hi all,

Sorry to post again...I need some advice! I have made a function to check a query (based on a LINKED table) to see if a value exists.

I am getting the 'Data type mismatch' error when running the code -

Private Sub cmdFind_Click()

Dim ChqString As String
Dim Acc As String
Dim BSB As String
Dim Chq As String
Dim qryAmount As Currency
Dim qryStatus As String
Dim qryRec As Date

Acc = Me.txtAccNo.Value
BSB = Me.txtBSBNo.Value
Chq = Me.txtChqNo.Value

ChqString = Chq & BSB & Acc

Me.txtchqstring.Value = ChqString

If Me.txtchqstring.Value = DLookup("Number", "querypayment") Then

qryAmount = DLookup("Amount", "querypayment", "[Number]=" & Me.txtchqstring.Value)
qryStatus = DLookup("Status", "querypayment", "[Number]=" & Me.txtchqstring.Value)
qryRec = DLookup("Received", "querypayment", "[Number]=" & Me.txtchqstring.Value)

MsgBox "etc......."

Else

MsgBox "Records do not indicate this cheque was received"

End If

End Sub

The error occurs when trying to do the DLOOKUP for 'qryAmount'.

I have checked the table design for the table the query is based on - and the field types indeed match up to those I have declared.

ie Amount is Currency
Received is Date/Time
Status is Text

I have also tried declaring them as String/Integer/Long etc - with the same error always occurring.

Have I taken the wrong approach with my code? Or is there perhaps something else I have overlooked?

Thanks again

Rob

 

[Rod D]

Hi Rob

As you are looking up a string value, the variable needs to be enclosed within single quotes. Try changing the Dlookup statement to :

qryAmount = DLookup("Amount", "querypayment", "[Number]=[B][COLOR="Red"]'[/COLOR][/B]" & Me.txtchqstring.Value[B][COLOR="red"] & "'"[/COLOR][/B])

If this works, then you will need to change the others as well.

Let us know how it goes.
Regards
Rod

 

[namliam]

Well to start off, What is this?? in your If statement??
Me.txtchqstring.Value = DLookup("Number", "querypayment")

I have never seen this and dont know what this result will yield.

In any case you have 3 DLookups which is (probably and relatively) slow
qryAmount = DLookup("Amount", "querypayment", "[Number]=" & Me.txtchqstring.Value)
qryStatus = DLookup("Status", "querypayment", "[Number]=" & Me.txtchqstring.Value)
qryRec = DLookup("Received", "querypayment", "[Number]=" & Me.txtchqstring.Value)

Try this:

Dim rst as DAO.Recordset 'Make sure to reference the Microsoft DAO reference.
' If you dont know how, search the forum, you will find it.

Acc = Me.txtAccNo.Value
BSB = Me.txtBSBNo.Value
Chq = Me.txtChqNo.Value

ChqString = Chq & BSB & Acc

Me.txtchqstring.Value = ChqString

set rst = Currentdb.openrecordset("Select * from querypayment where [Number]='" & Me.txtchqstring.Value &"'")

If rst.eof then 
    ' nothing found
else

    qryAmount = rst!Amount
    qryStatus = RST!Status
    qryRec = rst!Received

    MsgBox "etc......."

endif

set rst = nothing

Good luck

 

[systemx]

Hi all,

Rod - Thanks again. Concatenating fixed it up nicely and it now works a treat.

Namliam - Thanks for the advice. I had not considered using recordset to get at the query - it does make sense though so I will give it a whirl!

To explain this -

If Me.txtchqstring.Value = DLookup("Number", "querypayment")

Basically, the user enters 3 values in to boxes (cheque number, account number, BSB number from a cheque). These are then combined to make 'ChqString'.

The value of cheque string is written to 'Me.txtchqstring' as this needs to be displayed on the form when printed.

The Dlookup simply confirms whether the value exists in the query - it works reliable even without any additional criteria.

Thanks

Rob

EDIT: Should also point out that I am using ADO instead of DAO - not sure about the advantages/disadvantages of each - but I will modify this as needed.

 

[namliam]

Seems to me like If Me.txtchqstring.Value = DLookup("Number", "querypayment") is a strange way of doing it ...

I would (if I were using DLookup at all) store one of the Dlookups I need in a variant. Then check if this is null.
If not the simply get the rests, otherwise it dont excist.

ADO vs DAO, I am not savvy on that subject. But the reason I stick with DAO is that the Access database internaly uses DAO. Recordsets for forms and such are DAO recordsets (Me.Recordset and Me.recordsetclone)

ADO seems to be more 'native' to .NET and such... Which might be a reason to use that...