Long Date - Can't pull records accurately

  • Thread starter Thread starter Bonnie A
  • Start date Start date
B

Bonnie A

Hi everyone! Using A02 on XP. I get an extract from systems each week with
data that we use Access to analyze.

Of 4 date fields in my Access import table, 3 are looooong dates (04/21/2009
10:04:30 AM etc.) and one is a short date. The table shows them as Date/Time
fields but makes no format declaration in the field properties.

My problem is that I am trying to identify 'who has NOT had transactions
from/to'. For some reason, no matter which date field I use, my data is not
accurate. As I manually spot check my 607 records of data, I am showing 15
or 20 in the first 3 printed pages that DID have transactions during the
from/to period (I have a form with [BeginDate] and [EndDate] (they do have
short date formats). In my query, I have a field Processed:
Left([Processed_Time_Stamp],10) so I can then put this in the criteria line:
=[Forms]![fCriteriaFromTo]![BeginDate] And
([Forms]![fCriteriaFromTo]![EndDate]-1).
Click to expand...

Should I parse the dates into a field in the table so they are true 'short
dates'? Should I identify the format for each field in the table? It was
created when I imported the first file and I've not messed with it since.

But, I really need to be able to find the records that apply to this
statement: Which Active Contracts have had NO transactions from xx/xx/xxxx
through xx/xx/xxxx?

Can I code my query wordage better or reformat my From/ToForm date fields,
or format the table fields?

I would really appreciate it if someone could show me where I'm going wrong.

Thank you in advance for your time and assistance in the newsgroups!
 
Hi again,

I wanted to add that while my end goal is to identify who has NOT had a
transaction, the query I am working in and describe in my post is the base
query that pulls ALL transactions From/To. My next queries are comparing to
see who is an active contract and who is NOT on the list.

When I run the query, a number of contracts DO NOT APPEAR though they have 2
or more records that qualify. But they DO NOT APPEAR. If I then go to the
table and search for the contract number, I see records that meet the
criteria. It is maddening. Why do some records come in and others do not?
--
Bonnie W. Anderson
Cincinnati, OH


Bonnie A said:
Hi everyone! Using A02 on XP. I get an extract from systems each week with
data that we use Access to analyze.

Of 4 date fields in my Access import table, 3 are looooong dates (04/21/2009
10:04:30 AM etc.) and one is a short date. The table shows them as Date/Time
fields but makes no format declaration in the field properties.

My problem is that I am trying to identify 'who has NOT had transactions
from/to'. For some reason, no matter which date field I use, my data is not
accurate. As I manually spot check my 607 records of data, I am showing 15
or 20 in the first 3 printed pages that DID have transactions during the
from/to period (I have a form with [BeginDate] and [EndDate] (they do have
short date formats). In my query, I have a field Processed:
Left([Processed_Time_Stamp],10) so I can then put this in the criteria line:
=[Forms]![fCriteriaFromTo]![BeginDate] And
([Forms]![fCriteriaFromTo]![EndDate]-1).
Click to expand...

Should I parse the dates into a field in the table so they are true 'short
dates'? Should I identify the format for each field in the table? It was
created when I imported the first file and I've not messed with it since.

But, I really need to be able to find the records that apply to this
statement: Which Active Contracts have had NO transactions from xx/xx/xxxx
through xx/xx/xxxx?

Can I code my query wordage better or reformat my From/ToForm date fields,
or format the table fields?

I would really appreciate it if someone could show me where I'm going wrong.

Thank you in advance for your time and assistance in the newsgroups!
Click to expand...
 
Bonnie, my first thought is that if you are using the Left( ) function to
pull data out of a field, then the field is probably a string, not a date, or
is not being interpreted as a date. Try replacing left([fieldname],10) with
DateValue([fieldname])

The other thing you should probably do is declare the form controls as
parameters so that Access will process them as dates as well.

----
HTH
Dale



Bonnie A said:
Hi everyone! Using A02 on XP. I get an extract from systems each week with
data that we use Access to analyze.

Of 4 date fields in my Access import table, 3 are looooong dates (04/21/2009
10:04:30 AM etc.) and one is a short date. The table shows them as Date/Time
fields but makes no format declaration in the field properties.

My problem is that I am trying to identify 'who has NOT had transactions
from/to'. For some reason, no matter which date field I use, my data is not
accurate. As I manually spot check my 607 records of data, I am showing 15
or 20 in the first 3 printed pages that DID have transactions during the
from/to period (I have a form with [BeginDate] and [EndDate] (they do have
short date formats). In my query, I have a field Processed:
Left([Processed_Time_Stamp],10) so I can then put this in the criteria line:
=[Forms]![fCriteriaFromTo]![BeginDate] And
([Forms]![fCriteriaFromTo]![EndDate]-1).
Click to expand...

Should I parse the dates into a field in the table so they are true 'short
dates'? Should I identify the format for each field in the table? It was
created when I imported the first file and I've not messed with it since.

But, I really need to be able to find the records that apply to this
statement: Which Active Contracts have had NO transactions from xx/xx/xxxx
through xx/xx/xxxx?

Can I code my query wordage better or reformat my From/ToForm date fields,
or format the table fields?

I would really appreciate it if someone could show me where I'm going wrong.

Thank you in advance for your time and assistance in the newsgroups!
Click to expand...
 
Hi everyone! Using A02 on XP. I get an extract from systems each week with
data that we use Access to analyze.

Of 4 date fields in my Access import table, 3 are looooong dates (04/21/2009
10:04:30 AM etc.) and one is a short date. The table shows them as Date/Time
fields but makes no format declaration in the field properties.

My problem is that I am trying to identify 'who has NOT had transactions
from/to'. For some reason, no matter which date field I use, my data is not
accurate. As I manually spot check my 607 records of data, I am showing 15
or 20 in the first 3 printed pages that DID have transactions during the
from/to period (I have a form with [BeginDate] and [EndDate] (they do have
short date formats). In my query, I have a field Processed:
Left([Processed_Time_Stamp],10) so I can then put this in the criteria line:
=[Forms]![fCriteriaFromTo]![BeginDate] And
([Forms]![fCriteriaFromTo]![EndDate]-1).
Click to expand...

Should I parse the dates into a field in the table so they are true 'short
dates'? Should I identify the format for each field in the table? It was
created when I imported the first file and I've not messed with it since.

But, I really need to be able to find the records that apply to this
statement: Which Active Contracts have had NO transactions from xx/xx/xxxx
through xx/xx/xxxx?

Can I code my query wordage better or reformat my From/ToForm date fields,
or format the table fields?

I would really appreciate it if someone could show me where I'm going wrong.

Thank you in advance for your time and assistance in the newsgroups!
Click to expand...

One thing to clarify: Access Date/Time values are *NOT* stored as "long dates"
or as "short dates" or as text strings, or as ANY sort of formatted value. A
Date/Time is actually stored as a double float number, a count of days and
fractions of a day since midnight, December 30, 1899:

?Now; CDbl(Now)
10/28/2009 2:57:33 PM 40114.6232986111

Your Left() function is converting the Date/TIme value to a Text value, so
it's doing the comparison in *text* mode - and although the date 05/15/2009 is
in fact between 12/31/2008 and 12/31/2009, the *text string* "05/15/2009" is
less than either "12/31/2008" or "12/31/2009" (since 05 is less than 12).

Lose the Left() calculated field; a criterion of
=[Forms]![fCriteriaFromTo]![BeginDate] And < DateAdd("d", 1,
Click to expand...
[Forms]![fCriteriaFromTo]![EndDate])

will get all records from midnight at the beginning of BeginDate up to (but
not including) midnight at the end of EndDate.
 
Back
Top