Update Query

[manu]

I am trying to run an update query. Am using Date/Time field to define a criteria for this query but Access97 returns "Data Type mismatch error" every time I try running the query. The field I'm trying to update is a Text Field.

The Criteria statement allows for an update if the Date for that record is in a certain range.


All help is much appreciated.....

Regards,

 

[Hypernoodle]

Do you have an example of the query?

 

[manu]

Field called "InvoiceDate" contains date data
Field called "Year" is what I need to update

I would like a record with InvoiceDate between 01Jan2002 & 31Dec2002 to have Year Populated with "P1"

Hope this explains......

Attempting to run the query after criteria definition results in Data Type Mismatch error.

Data Type for "Year" = Text

Thanks for your help in advance

 

[Hypernoodle]

How about this...

Update TABLE
set Year = "P1"
where format(InvoceDate, "YYYY") = "2002"

 

[manu]

I did think about that, however, can you attempt with criteria define as "InvoiceDate Between 01/Jan/2002 AND 31/Dec/2002"

This is what causes the error prompt for me.....

 

[Hypernoodle]

The problem sounds like it's caused by your date format.

In your regional settings, what date format is specified for your "Short Date"?

 

[manu]

MM/DD/YY

 

[Pat Hartman]

If you use literal dates, you must surround them with pound signs - #01/Jan/2002# or #01/01/2002#

01Jan2002 is NOT a valid date format.

 

[Hypernoodle]

Update TABLE
set Year = "P1"
where InvoceDate BETWEEN #1/1/2002# AND #12/31/2002#

 

[manu]

Thanks Pat & Hypernoodle.......

I did sorround the date with Pound but am still getting "Data Type Mismatch in criteria expression

[FSAMATCH_A_UNIQ]![TAInvDate] Between "#1/1/2002#" And "#12/31/2002#"

Above is what my Criteria Line looks like:

FSAMATCH_A_UNIQ in the Table
TAInvDate is the field

 

[Rich]

Remove the quotes from your criteria

 

[manu]

Interesting observation, however, Access auto inserts the quotation marks......

 

[Rich]

Then your date field is a textfield

 

[manu]

no it's not...............I re-checked prior to replying

I'm very surprised myself.......

 

[Hypernoodle]

If it is a text field and you can't convert it, try this...

cdate(TAInvDate) Between #1/1/2002# And #12/31/2002#