Using Date as part of a unique field (1 Viewer)

[SteveE]

I have various users inputting data into excel sheets which are then imported into a database. I need to prevent duplicates and for this I use a UQFld:[DateFld]&[TimeFld]&[Booking]&[OtherRef]etc
at the point of importing if there are any of the fields missing the data row is not imported. Once all fields are complete the append query will upload the data as long as the UQfld is not already there. This works fine except where a users PC is not in the same regional format as the Date portion is local etc i.e 1/Aug/2011 will be 01/08/2112 on some but 8/1/2012 or 2012/08/01 etc
this makes my UQFld not unique, and can allow duplicate uploads
I wish to format the date field part to a standard just for this purpose. I am looking for advise as to the best way
ie Format([DateFld],"dd/mm/yyyy") but would a better method be?

 

[vbaInet]

Format() would be the way to go. But how are you actually importing data into Access?

 

[SteveE]

Thnaks for the reply
I am using transferspreadsheet range from saved xl version, inported into tem table, various routines run on temp table including making the UQ field, then appending to the live table. this would not be a problem but some data is entered directly into the db by the users this action also creates the UQ in the live table, so duplicates are avoided on appending. but as i said some dutch users have us format and the uk users have uk format dates
Would using date serial no or CLng([DateFld]) be the same in any country format of date?

 

[vbaInet]

Internally dates are stored in the US format. All that the Format function is doing is changing the representation of what is stored and as for CLng, it will give you the real numeric value of the date regardless of format. So I would suggest you stick to storing the date in one format and representing in the country specific format in your forms/reports.

 

[SteveE]

Ok, thanks for the updates I have decided to go with using the Clng function to create the UQfld Date portion of the string for the unique field , I have amended both the live version creation into this format and the appending, I will monitor how this goes.

 

[vbaInet]

Alright, keep us informed.

 

[SteveE]

Just a quick update UQ is generating fine for Holland and UK only problem is I overlooked the time field which is part of my UQ String Holland were using ie 9:30AM while UK was 09:30:00 dohh !! cured that with Format([TIMEStr],"Short Time")

 

[vbaInet]

CDbl() should have been fine too.

Good job!