Using a decimal (double) in findfirst

[MarionD]

Hi there,

Hope someone can help me out here:

I have a table tblxx_Fees in which there is are 2 fields Hrs(double) and Amount (currency)

Hrs Amount
1 10€
1,5 17€
,5 4€

etc.

Now when in the billing ,5 is entered as the Hrs, I want to check if there is any Fee available for the number of hrs.

Set recs = db.OpenRecordset("Select * from tblxx_Fee where tbl_KIGA_ID=" & Me.Parent!tbl_kiga_id)
recs.FindFirst "[Hrs]= " & Me.Hrs
If recs.NoMatch Then.....

This works fine for whole numbers, but the error I get says "Syntax error (comma) . Is there anyway around this?

Thanks
M

 

[vbaInet]

Did you format the textbox?
And do you know you could also use the DLookup() function?

 

[MarionD]

Hi!

The Dlookup is notoriously slow. I prefer the findfirst as I don't need to find a specific record, only to know that a fee is available in the recordset I have selected for ,5 an hour. (For eg. if they enter ,75 I want a "nomatch" then an error message telling them that ,75 hours cannot be billed)

I have tried CStr(Hrs) but it doesn't work either as the Hrs in the table is Double.

Thanks
M

 

[vbaInet]

Yes it is slow! I'm just making sure that you're aware of it.

To improve the performance of your recordset you can also add dbOpenSnapshot to the recordset type.

CDbl() is what you need to cast to double.

 

[MarionD]

Hi there
Found it! The secret is to remove the comma and replace with period.

anz = Me.AnzahlStd
anz = Replace(anz, ",", ".")
Set recs = db.OpenRecordset("Select * from tblxx_Beitrag where tbl_KIGA_ID=" & Me.Parent!tbl_kiga_id)
recs.FindFirst "[AnzahlStd]= " & anz
If recs.NoMatch Then
MsgBox "Die angegebene Anzahl der Stunden nicht in die Gebürenordnung enthalten.", vbInformation, "KINDY"

 

[vbaInet]

I thought your Regional Settings uses comma as the decimal point?

 

[MarionD]

Thats why I replace the comma with a period, then it works