IF Statement exact match??

[stemac23]

Hi

I'm just wondering if it is possible to put an exact match filter on a IF Statemenet in a VBA Form. Basically i'm wanting to search for a postcode on a table using a txtbox on my form, i only want it to show an exact match and if it cant find it display an error message?

Currently I have the if statement below searching if the txtbox has something in:

If PCDESearch > 0 then
me.filter = "POST_CODE = '" & me.PCDEsearch & "' "
me.filteron = true
else
myinput = msgbox("Please Enter Postcode", vbCritical + vbOKonly, "" = vbOK
End if

then under the above I have another IF statement which i only want it to do if the above it true.

If liveservice = "LIVE" then
me!txtav = me!Availablitydate
else
me!txtav = me!Availablity_date
End if

Any suggesting???

 

[DCrake]

Have a look at the DLookup() function

 

[ghudson]

I would use the DCount() function to see if a value exists in a table. If DCount() > 0 then true, if not then false.

 

[stemac23]

This hasn't work, its showing a compile error. After reading about the DCount i'm unsure this will work, I'm wanting to display all the records with an exact match then use another if statement if the 1 above is true.

Will the DCount work for the above?

Thanks

 

[SteveSchapel]

Stemac,

If I understand you correctly, then the advice about DCount is good. If you are getting a compile error, it would help others to help you if you could show us what you tried.

What I imagined was something along these lines...

   If DCount("*","NameOfYOurTable","POST_CODE = '" & Me.PCDEsearch & "'") Then
      ' do whatever
   Else
      MsgBox "This Postcode does not exist in the database."
   End If

By the way, I notice something in your initial code that seems incorrect.
If PCDESearch > 0 Then
Yet elsewhere the code suggests that PCDSearch will be text. So evaluating it against zero is asking for trouble.

 

[stemac23]

That works brilliantly! Thank you