dlookup syntax 1

[osurfc]

I have a macro that runs looking for patient_ID where it is equal to medrec on a dataentry form.

Functioning code follows

DLookUp("[SS_PATIENT_ID]","dbo_demographics","[Patient_id] = " & ' [Forms]![frmdataentry]![medrec]')

The admin responsible for dbo_demographics changed the length from 6 to 13 padding with preceeding zeroes, and data entry folks are asking to be able to put in non-zero values
I cant get the syntax correct using
'format([Forms]![frmdataentry]![medrec]', "0000000000000")

Any help appreciated

Thanks
Mark

 

[dhookom]

I can't imagine that function working. Did you copy and paste this? I would expect to see something like

DLookUp("[SS_PATIENT_ID]","dbo_demographics","[Patient_id] = '" &  [Forms]![frmdataentry]![medrec] & "'")

I don't know what any of this has to do with the zeroes.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

DLookUp("SS_PATIENT_ID","dbo_demographics","Patient_id='" & Format([Forms]![frmdataentry]![medrec],"0000000000000") & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[osurfc]

Duane sorry about confusion, the functioning code I have was

DLookUp("[SS_PATIENT_ID]","dbo_demographics","[Patient_id] = " & ' [Forms]![frmdataentry]![medrec]')

I had to pattern match my 7 digit medrec to the patient_id at 13 digits. I have to pad my medrec with leading zeroes to make it function. I abbreviated what I needed from the equal sign in the dlookup statement. PHVs' posting after yours was what I was looking for.

 

[dhookom]

I am very surprise the following will work. Maybe macros have some functionality that I am not aware of. I expect this would throw an error in VBA.

   ...,"[Patient_id] = " & ' [Forms]![frmdataentry]![medrec]')

Duane
Hook'D on Access
MS Access MVP