Need to convert a string to integer but it isn't working

[HarrySpencer]

Hello,

I've been using the following code for ages but wanted to change the fields being search because if the search field(NiNo) is blank it wouldn't return any results or jump to that record. Now trying to use the PersonalID field but having problems matching the data types.

The code runs when double clicking on one of the results fields from a continous form that use a query to search the DB based on surname.

Dim rst As Recordset

Set rst = Forms![MainPage_fm].RecordsetClone
rst.FindFirst "[NiNo]='" & Me![txtNiNo] & "'"

If rst.NoMatch = False Then
Forms![MainPage_fm].Bookmark = rst.Bookmark
End If
DoCmd.Close acForm, "SearchDOB_cfm", acSaveNo

changed line 4 to...

rst.FindFirst "[PersonalID]='" & Me![PersonalID] & "'"

but get a data type have tried to use the Val function but still having problems.

Any ideas would be greatly appreciated? Cheers.

 

[Galaxiom]

If Me.PersonalID is a number it won't need the single quotes.

 

[HarrySpencer]

Great that's work great thanks for your help

here is the code if anyone is interested

Dim rst As Recordset

Set rst = Forms![MainPage_fm].RecordsetClone
rst.FindFirst "[PersonalID]=" & Me![PersonalID]

If rst.NoMatch = False Then
Forms![MainPage_fm].Bookmark = rst.Bookmark
End If
DoCmd.Close acForm, "SearchSurname_cfm", acSaveNo

 

[CBrighton]

If Me.PersonalID is a number it won't need the single quotes.

I thought it was based on the datatype of the field rather than the contents of the control?

 

[vbaInet]

I'm sure he meant the field [PersonalID]

 

[CBrighton]

OK, just making sure I haven't got it the wrong way round in my head.

 

[Galaxiom]

I thought it was based on the datatype of the field rather than the contents of the control?

Most definitely.

What I should have said but lost accuraccy when carelessly condensed using "it":

"If PersonalID is a number, Me.PersonalID won't need the single quotes."

BTW. It is a personal preference but I avoid using explicit comparisons with Boolean values. I think they are easier to read and more consistent in the alternative where the If is applied directly to the boolean property.

If rst.NoMatch = False Then
can be expressed as:

If Not rst.NoMatch Then

The true comparison is even cleaner:

If rst.NoMatch Then

Moreover because zero is the equivalent to Boolean False, there are other expressions that are not technically Boolean which can compare implicitly.

For example:

If whatever <> 0 Then

can be replaced by :

If whatever Then