I am now using VB6 and Access 97. I have always been able to use date fields as part of a primary key if needed. I do not seem to be getting the results i want when using the seek command on a table. I have not been able to find anywhere that says date fields are not to be used in primary keys.. any help would be appreciated.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
date fields as part of a primary key in access 97 ??? 1
- Thread starter 1712
- Start date
-
1
- #2
Whenever you use a date field in the key, the trick is getting the formatting right when you use a where clause on that field. For example, if you say:
Select * From mytable Where dtDate = '01/01/01 1:00 am'
this might fail to return what you want. The reason is the datebase might actually have something like '01/01/01 1:00:00.0010' for the date; it may have fractions of seconds. If you select the date with a query, you won't get the fractional seconds. So then even if you store the date in a date field in VB and then use it in a query, it won't make a match.
One way around this is to avoid using dates in a key, but that often isn't possible or practicle. If you must, try doing your query like this:
Select * From mytable Where Convert(dtDate, varchar(20)) = '01/01/01 1:00 am'
This should work because the Convert function should drop the milliseconds. You can always do more with the Convert fucntion to look at just the parts of the date that matter. Hope this helps.
Select * From mytable Where dtDate = '01/01/01 1:00 am'
this might fail to return what you want. The reason is the datebase might actually have something like '01/01/01 1:00:00.0010' for the date; it may have fractions of seconds. If you select the date with a query, you won't get the fractional seconds. So then even if you store the date in a date field in VB and then use it in a query, it won't make a match.
One way around this is to avoid using dates in a key, but that often isn't possible or practicle. If you must, try doing your query like this:
Select * From mytable Where Convert(dtDate, varchar(20)) = '01/01/01 1:00 am'
This should work because the Convert function should drop the milliseconds. You can always do more with the Convert fucntion to look at just the parts of the date that matter. Hope this helps.
- Thread starter
- #3
Steve,
The convert function you mention is apparently a SQL server function and not an access 97 function. I could be wrong but I could not find a trace of it.
I could not find a different way through any other function to look at just the date field and not the time fields which I suspect is the problem, so I created a clone field that shows the date field as a string and only with the date info to be used in my primary key but it also does not work. I am opening my table with the dbopentable property and have been successful before but I believe this date field is the problem for some reason
Charlie
The convert function you mention is apparently a SQL server function and not an access 97 function. I could be wrong but I could not find a trace of it.
I could not find a different way through any other function to look at just the date field and not the time fields which I suspect is the problem, so I created a clone field that shows the date field as a string and only with the date info to be used in my primary key but it also does not work. I am opening my table with the dbopentable property and have been successful before but I believe this date field is the problem for some reason
Charlie
listener22
Programmer
are you using #'s around the date, and is the search date you are providing
in the default format of windows on the pc? Jim
oracle, vb
in the default format of windows on the pc? Jim
oracle, vb
- Thread starter
- #5
- Status
