Long Date - Can't pull records accurately

[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).

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!

 

[Bonnie A]

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

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).

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!

 

[Dale Fye]

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

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).

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!

 

[John W. Vinson]

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).

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!

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,

[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.