Strange ID Problem

[GendoPose]

Hi All,

Having a very odd problem with my Autonumber ID.

In my database there is a table of customers which drives a combo box in a form, and there are nearly 2500 customers in this table.

I searched for a specific customer and I got 2 different customers come up, and I've found the reason for this but I can't figure a solution for the life of me.

What's happening is that I have a customer with the ID 2886 and another with the ID 886, and when I search for the customer with 886, the one with 2886 shows in the search results too. I've tried setting the format of the ID field to "0000" to force it to 0886 and 2886 but that didn't work and I can't think for the life of me what else to try.

Any help please?

Thanks

 

[Galaxiom]

How are you doing the search? If it is the one in the record navigation bar then you are expecting too much of it.

Every serious database front end offers dedicated search forms.

 

[CJ_London]

How are you searching for your customer? If you tell us the reason you are getting two customers, we may be able to suggest a solution. Formatting won't work since this is a display feature whereas the search will be on the underlying data (unless you are using the format function in your search criteria)

 

[GendoPose]

How are you doing the search? If it is the one in the record navigation bar then you are expecting too much of it.

Every serious database front end offers dedicated search forms.

Yes my search is from a form, I specify various criteria from text and combo boxes and hit search to open a report.

 

[nanscombe]

Are you building the criteria and using 'Like' ID somewhere?

I would suggest that you "Debug.print" the result of your criteria building, to the immediate window, or output it to a MsgBox to see what is actually being passed.

 

[GendoPose]

Are you building the criteria and using 'Like' ID somewhere?

I would suggest that you "Debug.print" the criteria, to the immediate window, or output it to a MsgBox to see what is actually being passed.

The criteria I'm using is

Not Is Null And Like "*" & [Forms]![formname]![comboname] & "*"

How do I do that?

 

[CJ_London]

Like "*" & [Forms]![formname]![comboname] & "*"

That is your reason

change to

= [Forms]![formname]![comboname]

 

[nanscombe]

Is it a combo box of names that returns an ID (number)?

If so you might try

 = nz([Forms]![formname]![comboname], -1)

That would return either the value in [comboname] or -1 if it were null / unselected. Assuming you don't have a record with an ID of -1. I sometimes use a value of -999, instead of -1, so I know how to trap it if needs be.

 

[GendoPose]

That is your reason

change to

I've used this before but it doesn't return blank results if that field hasn't got a value in the record, and it also means you have to specify another criteria to get that to work.

Is it a combo box of names that returns an ID (number)?

If so you might try


Code:
= nz([Forms]![formname]![comboname], -1)
That would return either the value in [comboname] or -1 if it were null / unselected. Assuming you don't have a record with an ID of -1. I sometimes use a value of -999, instead of -1, so I know how to trap it if needs be.

I'll give this a try.

 

[GendoPose]

Is it a combo box of names that returns an ID (number)?

If so you might try

 = nz([Forms]![formname]![comboname], -1)

That would return either the value in [comboname] or -1 if it were null / unselected. Assuming you don't have a record with an ID of -1. I sometimes use a value of -999, instead of -1, so I know how to trap it if needs be.

This returns the result when the combo is selected but when nothing is selected, the query is blank.

 

[nanscombe]

Then you may need to wrap nz() around the other side of the criteria as well.

nz(ID, -1) = nz([Forms]![formname]![comboname], -1)

That assumes that the field ID is the foreign key (link) to another table)



If however you have a default value for ID in your table, ie 0, you would use that instead of -1 in the original version instead.

ID = nz([Forms]![formname]![comboname], 0)

 

[GendoPose]

Then you may need to wrap nz() around the other side of the criteria as well.

nz(ID, -1) = nz([Forms]![formname]![comboname], -1)

That assumes that the field ID is the foreign key (link) to another table)



If however you have a default value for ID in your table, ie 0, you would use that instead of -1 in the original version instead.

ID = nz([Forms]![formname]![comboname], 0)

Sorry you were right the first time, I mistyped something in the code. The NZ-999 code works perfectly, thank you!

 

[CJ_London]

I've used this before but it doesn't return blank results if that field hasn't got a value in the record, and it also means you have to specify another criteria to get that to work.

In that case you need to use nz on the other side of the criteria i.e.

nz(myField)=nz([Forms]![formname]![comboname])