Format of Autonumber

[CumbrianCanary]

I have two forms which contain entries which have an AutoNumber field, the tables and hence the forms are not related. Both the AutoNumbers have a format associated with them so when the user sees them in the form they look like:

ABC001 0r XYZ001

Both forms also have a date in them. I have set up a union query that checks both sets of data and displays any in separate form which fit my search criteria. The problem is that I lose the format of the field. In other words I just see 1 and 1 rather than ABC001 and XYZ001, so the source of the data can not be identified.

Does anybody no of a way that I can keep the format of these fields after I have run the query?

Many Thanks
CumbrianCanary

 

[KenHigg]

I think you're trying to use autonumber in a way that it was not intended to be used. I'll spare you my 2cents - I suggest you search the forum on the topic...

 

[CumbrianCanary]

I think you're trying to use autonumber in a way that it was not intended to be used. I'll spare you my 2cents - I suggest you search the forum on the topic...

I am not sure why you think that. I have looked at the search and can't find anything that helped me out with my problem which is why I asked for some help. I am using the autonumber to generate a unique number for each entry. As this databse is being used a large number of people it seemed to be the best way of doing this.

 

[KenHigg]

Hum...

So the autonumber is giving you the 001, etc. Where is the ABC part coming from?

 

[CumbrianCanary]

Hum...

So the autonumber is giving you the 001, etc. Where is the ABC part coming from?

The autonumber generates the basic number. I have then added within the field property that the format should be "ABC"000 or "XYZ"000. This then returns ABC001 or XYZ001 in the field for the individual forms. This works fine.

It is when I want to do a date check on both forms and merge them into one form the problem rears its ugly head. This form gets its data from a union query that searches when entries are coming up to a certain date. It lists the correct fields but the prefix is lost. What I would like to know if it is possible for these numbers to keep their format from the orginal form to the new one. This would allow the end user to see if the ou of data entry is from the XYZ or the ABC form.

Sorry if this is somewhat confusing!

 

[KenHigg]

Where exactly are you applying this 'format' and what does the pc of code look like?

 

[CumbrianCanary]

Where exactly are you applying this 'format' and what does the pc of code look like?

The format is in the format section within the property of the filed. It looks like I mentioned earlier "ABC"000

 

[KenHigg]

In form design or table design?

 

[Pat Hartman]

You have to format the field in the union query:

Select "ABC" & Format(YourKey, "000") As FormKey, fld2, fld3, etc.
From YourTableABC
Union Select "XYZ" & Format(YourKey, "000") As FormKey, fld2, fld3, etc.
From YourTableXYZ;

 

[CumbrianCanary]

You have to format the field in the union query:

Select "ABC" & Format(YourKey, "000") As FormKey, fld2, fld3, etc.
From YourTableABC
Union Select "XYZ" & Format(YourKey, "000") As FormKey, fld2, fld3, etc.
From YourTableXYZ;

Pat, Many thanks for that. It has worked a treat.