Hi there,
Hope somebody could help me.
Following is the issue.
I have a VB code that checks the date in an *.mdb file, comparing system date with expiration date contained in the same *.mdb.
Indeed the current data, got from system, is written in this *.mdb.
All is working fine when the system date format *.mdb format for date are the same.
All is not working when the two formats are different.
(ex: US format and European format)
The software expires since it finds a different date that the one set.
Please, has any of you ever encountered a similar situation ?
Have you got any idea of how solving this issue ?
Ideal solution would be putting the two dates in the same format.
Many thanks
Have a great Sunday
Where is the date data coming from that it would have two different formats? Is it being entered manually? Linked to another db or source? Imported?
Which format is the system date? US? European?
Why can't the dates be modified to agree in format before it gets to Access?
If this is older data, you could do an update query to change the format of the dates to be consistent with the system date. If this is new data that is being imported, then you need to run a check on the date formats and update prior to importation if possible, or import to a temp table, run an update query and then an append query. Just some thoughts with out having adequate information to give you a definitive answer.
Alan
Last edited by alansidman; 02-27-2011 at 08:34 AM.
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
Hello, many thanks for your prompt answer.
Apologizes for not being clear enough.
The date data are coming.
1 - *.mdb They are written manually in the mdb once the mdb is issued.
2 - in the code are got from the system by code instruction.
Then the data from the system is written in mdb database to be compared with the expiration date.
- The format is European
Indeed the aim of the thread was to know if it is possible to make the two dates agree before entering in access
I suppose that the most logical solution should be as you kindly suggest
running a check on the date formats and updating prior to importation
or
import to a temp table, run an update query and then an append query.
Should the pieces of information provided be enough, my question now is
- how to realize the check on date formats
and
- how to run the update query
- in order to uniform the two data ?
Many thanks
Let's take these one step at a time.
If the dates are being manually entered into the database, ensure that all entries are made in the European Format using an input form. Under no circumstances should you allow the user to enter data directly into tables. One way to ensure proper format is to set your properties to the format desired. Additionally, I like to use Allen Browne's calendar function to ensure consistency. Here is a link to that
http://allenbrowne.com/ser-51.html
For the older items in your table, create a query that has the record id and the date fields. In a new field, create an expression using an expression such as
=day(DateField)&"/"&Month(DateField)&"/"&Year(DateField)
This will put all the dates in the European format (if I got the sequence correct).
Save the query as a select Query. Now change it to an update query.
Look at this video on how to create an update query.
http://www.bluemoosetech.com/microso...Update%20Query
This final video on data manipulation may help also.
http://www.datapigtechnologies.com/f...anipulate.html
Good luck and post back with any specific question or issues you may encounter.
Alan
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks