Solved Dlookup custom Autonumber - doesn't find value

[RickHunter84]

Hello all,

i have a quick question, I'm playing with a database trying things and i came across something that i wasn't expecting, here is the issue:

i noticed that ms access provides has an option to generate a custom autonumber, so i switched on (input custom sequence)to try the functionality.
when i try to open another form (by searching with vba) using the custom autonumber for example: G10016, dlookup doesn't find it. i looked at the table that stores the information and the value gets "stored" as 10016, so next i naturally tried searching for 10016, it didn't find it either, but when i use 16, it works.

any idea on how to have access recognize the whole value "G10016"? let me know if further context is required.

thank you in advance.

Rick

 

[theDBguy]

Hi Rick. Sorry, but is this a new feature in O365?

 

[RickHunter84]

hello theDBguy,

no that im aware of, im using plain access 2016. I just posted some picture for better context

Rick

 

[RickHunter84]

 

[Mike Krailo]

Best to use a regular autonumber and if you need that format, just create another column with your special PO Number. Leave the auto number alone.

 

[RickHunter84]

Thank you Mike Krailo. i just found information in stackoverflow plus your recommendation have made me rethink the table structure. I think i know which way to go now. here is the info for reference:

Existing comments all contain good information, but it can be useful to put it all together. The comments also failed to describe the context in which Access automatically copies and applies properties like Format, so that the comments (even if correct) might seem contradictory. My explanation is a bit verbose, but hopefully avoids further confusion.

The purpose of the Format property for any value in Access is to define how the data is displayed. This is true of a table column presented in a datasheet or a textbox control on a form. The Format does not define how values are stored, either in storage or in memory. The same value could be formatted and displayed differently without affecting the underlying stored datum. In this case, the Autonumber values are really Long Integer values. (They are not stored with a preceding "A-", which would require the values to be strings and would ruin Access's ability to automatically increment the values.)

Access attempts to provide a consistent view of the data and reduce tedious programming details by automatically copying the Format property to queries and form controls, just as it does with many other metadata properties. For instance, if you drag the AutoNumber field onto a form in design mode, it will automatically copy the Format string from the column to the TextBox control's Format property. In contrast, if you include the same column in a query, the query's column property sheet also has a Format property, but it will remain blank by default. However, when the query is executed, it will indeed be displayed with the format defined on the table column. This behavior does not mean that the data values themselves "have a format", rather Access is just doing its automatic work of looking up default formatting values from the table definition and applying it to the query's output. (It can do this if there is a simple one-to-one table column to query column relation, which is the usual case for queries.)

DLookup() is a Visual Basic (VBA) function. It is necessary that such functions handle the "raw" data independently of metadata, like Format (or Caption, Text Alignment, etc.). For coding purposes, a programmer expects to retrieve the actual long integer value from the column, not a formatted string value like "A-00001". The function will not only skip the format, the formatting information is completely dropped from data values. In a programming environment, data can be combined and manipulated and the concept of "format" becomes lost and/or meaningless. Even though in this case it might seem obvious, DLookup makes no assumptions about what you're going to do with the data and so just returns the integer values.

If your form TextBox control was not originally placed on the form specifically for the AutoNumber field, Access would not know to the copy the Format property. It would just display the integers from DLookup() as basic integers. However, you can manually set the TextBox's Format property to match the table column's Format property exactly to get back the expected values. -Source

thank you and best regards,

Rick

 

[theDBguy]

Hi Rick. Glad to hear you got it sorted out. Cheers!

 

[Pat Hartman]

You figured out that a Format is used only on display. It does NOT affect how data is stored. Keep that in mind for decimals and dates since it applies across the board. It is poor practice to ever format data at the table level since all that does is to obscure the actual contents of the field. The one possible exception is the Percent format. That one will help with data entry as the field is used in forms. it allows you to enter 5.4 and have that stored as .054.

You said you have your solution but I don't see it in your explanation. You also need to be using dMax() rather than dLookup() or dCount(). So, I've attached a database with two examples of how to generate custom sequence numbers.