FindFirst Does Not Work with Decimal Data Type

[JonMulder]

I've got a bunch of tables that I'm linking to that have Primary fields as a Decimal Data Type. Actually, ALL of the number fields are of Decimal Data Type. I want to do a "FindFirst" to locate the first (and only) record that has, for example, "MASTER_SITE_ID = 49033". Prior to the FindFirst, I have some code to get the user to select a MASTER_SITE_ID from a listbox, then define a Variant variable for the Decimal Data Type. Here's a snippet of my code:

Private Sub CmdAddGridWell_Click()
Dim booWellNotInGridWellTable As Boolean
booWellNotInGridWellTable = True
Do While booWellNotInGridWellTable
DoCmd.OpenForm "GetWellLocation_CASGEMWells", , , , , acDialog
If pubvarMASTER_SITE_ID = "" Then
Exit Sub
End If
'Check that CASGEM Well Record is not already linked to a GridWell Record.
Dim dbs As Database
Dim rstGridWells As Recordset
Set dbs = CurrentDb()
Set rstGridWells = dbs.OpenRecordset("Query--GridWells", dbOpenDynaset)
rstGridWells.FindFirst "MASTER_SITE_ID = " & CDec(pubvarMASTER_SITE_ID)
''' The following code did not work.''' rstGridWells.FindFirst "MASTER_SITE_ID = '" & pubvarMASTER_SITE_ID & "'"
If rstGridWells.NoMatch Then
Exit Do
Else
Dim intResponse As Integer
Call PlaySound("OhOh.wav")
intResponse = MsgBox("This well is already linked to a Grid Well." & vbNewLine & "Do you want to view well data?", vbYesNo, "Well Already Linked to a Grid Well")
If intResponse = 6 Then
DoCmd.OpenForm "Form--ViewWellData"
End If
Exit Sub
End If
Loop
DoCmd.OpenForm "Form--EditGridWellData", , , , acFormAdd, acDialog
End Sub

I've tried using the CDec function, but that doesn't seem to work. I've read a lot about how Decimal Data Types are not very "VBA-friendly", and I would prefer not to have to deal with them. However, unfortunately, I am LINKED to these tables and have to deal with them as they are.

Any suggestions on what I can do, or what I'm doing wrong? Thanks in advance for any help!

Jon Mulder
Engineering Geologist, California Department of Water Resources, Red Bluff CA

 

[JHB]

Try the below line:

 rstGridWells.FindFirst "CLng(MASTER_SITE_ID) = " & pubvarMASTER_SITE_ID

''' The following code did not work.''' rstGridWells.FindFirst "MASTER_SITE_ID = '" & pubvarMASTER_SITE_ID & "'"

No because you are treating "pubvarMASTER_SITE_ID" as a string

 

[JonMulder]

Using the CLng function did the trick! Thanks very much for your help!

Much appreciated!
Jon Mulder, Geologist
Red Bluff, CA USA

 

[DavidAtWork]

Just out of interest why would you use a decimal number as a primary key instead of an integer or long integer type number. It's not surprising to see that find first doesn't work as even though you may specify a decimal with x number of places, the database still stores this the value to umpteen decimal places especially when it's a calculated value

 

[JonMulder]

I'm linking to a bunch of tables that consultants designed for us for a web application. I have no idea why the consultants used "decimal" data type for EVERY numeric field in their tables. It seems very odd. I thought that maybe they needed decimal types for the web aspect of the project.

Jon Mulder