[email protected]

  • Thread starter Thread starter Haji
  • Start date Start date
H

Haji

Hello,

I am using a program that is dumping data from Quickbooks
to Access. Quickbooks has some custom fields in a that
we created that include Insurance, Discount and
Birthday. Many of the people have missing data in these
fields. For instance, it is not uncommon for someone to
have a missing Insurance company, but a discount and a
date. What this program does is to download the first non-
null value into CustomField1, the second non-null value
into CustomField2 and the third into CustomField3 into a
Customer Table. So if all fields are filled out for John
Smith in this Customer Table, Allstate will appear in
CustomField1, 10% (for the discount) in CustomField2 and
12/20/03 in CustomField3. However, if the Insurance is
missing, 10% will appear in CustomField1 and 12/20/03
will be in CustomField2.

I am trying to do is to run a query capture only dates
that appear in either CustomField1, CustomField2 or
CustomField3. I wrote a between query Between 1/1/01 and
12/31/03 but something went wrong. I did not get all of
the birthdays and I did get some of the discounts.
Basically I got everyone whose birthday started with a 1
(for January) and all of the 10%s. Can anyone help me
with this?

Thanks,

Chuck
 
It sounds like the downloaded fields are Text data type instead of
Date/Time. Make a copy of the table and see if you can successfully change
the data type. If you get no errors, then your search should work.

If the data type is date/time already, then the problem is with your
criteria. Note that you must include special delimiters to tell Access that
you're providing a date or time.

Between #1/1/2001# And #12/31/2003#

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
You don't mention it, but the fields are presumably Text in order to
accommodate numbers or dates or names. So your query needs to check
whether the contents of the field represent a date.

It sounds as if you need something like this:

SELECT CustomerID, CustomField3 As Birthday [, other fields if needed]
FROM CustomerTable
WHERE IsDate(CustomField3)
UNION
SELECT CustomerID, CustomField2 As Birthday [, other fields if needed]
FROM CustomerTable
WHERE IsDate(CustomField2)
UNION
SELECT CustomerID, CustomField1 As Birthday [, other fields if needed]
FROM CustomerTable
WHERE IsDate(CustomField1)
[ORDER BY whatever];
 
Back
Top