update query

[frustratedwithaccess]

Hello All,

I have two columns in a table (Table1), SamDate and DOW. SamDate contains a range of dates displayed ddd", "mmm d", "yyyy (i.e. Mon, Feb 26, 2015), and DOW contains a corresponding day code. The day codes are as follows

1 = Sunday
2 = Monday


3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday

What I am attempting to do is update the DOW column based on the SamDate column. Below is one attempt I have made to update the DOW column:

Update Table1
SET DOW = 1
WHERE SamDate = Sun;

My plan was to run the update query with the desired day code and SamDate day replaced as needed. This has not been working for me. Any suggestions are appreciated!

Thanks

[Rawb]

Are you saying that the SamDate Column contains multiple formats? If that's the case, then you're not saving the data as a Date, you're saving it as a text string (which can be bad, especially if you're allowing multiple input formats).

Can you give some example data from SamDate?

[frustratedwithaccess]

SamDate is saved as one format as given in the example in my first post (i.e. all dates appear in this format Mon, Feb 26, 2015 in the "datasheet view"). In the "design view" I have set the data type as Date/Time and the format as ddd", "mmm d", "yyyy

[frustratedwithaccess]

so in short I am trying to update the dow column based on the day portion of date from the SamDate column

[orange]

Why? Use the facilities of the dbms. Date data type for dates. You can always format the display anyway you want.

Research MsAccess weekday()

[frustratedwithaccess]

I was unaware of the weekday function. Thanks

Any tips on how to use this with my above question is much much appreciated

Thanks again