Access Table Date Conversion

Factual Questions
1

I am not sure if this goes here or in IMHO but here goes.

I have a Access table with a date column that is formatted with a range of dates from 1/01/2007 to 7/31/2007. It is a text field.

I am trying to create an Update query to change the values from i.e 1/01/2007 to 20070101.

I have created the query. I have the Date Filled as the Field and I have my table selected.

I have Format([DATE FILLED],“yyyymmdd”) in the criteria and update to is DATE FILLED.

When I run the query it tells me zero fields will be updated.

What am I doing wrong?

2

maybe because you designated it a “text” field? isn’t there a designation “date” for the fields, even tho you are using a custom format? I’m somewhere between a beginner and an intermediate user, but I’ve never had a problem getting my queries to work

3

In your update query, you should have

row 1 (Field) = DATE FILLED
row 2 (Table) = tablename
row 3 (Update To) = Format([DATE FILLED],“yyyymmdd”)
row 4+ (Criteria) = blank

I just tried this and it worked fine - replaced the old value (1/1/2007) in text field DATE FILLED with the new value 20070101.

If you have your FORMAT line filled in on the Criteria, it’s not going to find anything, because the current data won’t match what’s in the Criteria.

One note - it’ll be a lot easier if you don’t use spaces in your column names. Try using DATE_FILLED instead. Then you don’t get mucked up with the brackets.

4

Ohhhh okay. Now I see what I was doing wrong. Thank you!!! I kept the thinking the “Update to” was asking me where I wanted the updated data to go. Got it now.

I googled and I could not find anything that stated it that clearly.

Edit: I tried it and worked perfect. Thanks again! :slight_smile:

5

Yup, the query layouts can be a bit confusing sometimes.

Glad that helped.