Update Query

[GtrJim]

I am needing to update a table field.

I am using a query but keep getting "Parameter Value" requests. Can anyone help with a solution.

Public Sub CodeTests()
Dim dbs As Database, qdf As QueryDef, sSql As String
Dim dteMon As Date
Dim sMon As String
Dim iMon As Integer
Dim iLMon As Integer
Dim iYear As Integer
iMon = Month(Date)
sMon = MonthName(iMon, True)
iLMon = iMon - 1
iYear = Year(Date)
Set dbs = CurrentDb

DoCmd.RunSQL "UPDATE tbl_Sales_Data Set tbl_Sales_Data.[Date] = tbl_Sales_Data.[Day]" & "-" & sMon & _
" Where tbl_sales_Data.[Date] = isnull;"

End Sub

[XLGibbs]

If you place a column name in brackets, and it is not an actual column in the table, it would prompt for a parameter.

Are the [Date], [Day] columns actual columns?

I am not sure what you are exactly trying to update, but it looks like you are trying to update a date column with a new format which includes the month name.

There are way, way better days to format date strings that having to go through all that..

1. Is the [Date] column an actual datetime datatype? or is it a string?

2. Givent the built in flexibility of being able to manipulate the format of datetime data types, it doesn't seem practical to update the field in this way (or to have separate columns for [Day] if that is the case.

3. What is the datatype of the [Date] column, what is sample value from that column, and what is the desired result of your update?

I am assuming somewhere in the code you are establish a connection with with do be allowed to run DoCmd..

With CurrentDb for example in Access vba.

If from a VB program, you would need to set the connection string as well.

[alimcpill]

Agree with everything XLGibbs says. However, I think the

" Where tbl_sales_Data.[Date] = isnull;"

looks suspect. If you want to update where the Date field is null, then that code should probably read either

" Where tbl_sales_Data.[Date] is null;"

or if you want to use isnull, it would have to look like this:

"Where isnull(tbl_sales_Data.[Date])";

because isnull() is a function.

[XLGibbs]

WHere tbl_Sales.[Date] is null is correct.j thought isnull was a typo

Incidentally, you should never use the function (or any function) isNull() in a WHERE clause, and if you do use it, it has to be qualified with true of false (e.g. IsNull(Column) = 1)

[GtrJim]

Thanks all, sorry about the delay responding.

I have several fields two of which are Date and Day. Right now the Day column is integer and the Date column is date type. I am actually pulling an Excel file and trying to add a date field that does not exist in the excel file. I was able to add a single integer but when I tried to add more I kept getting errors.

What I actuallly want to happen is for the Date field to be a date, month/day/year. The Excel file has no date in it but the sheet names are 1-31 for each day of the month. Based on the day of the month data is pulled from a different sheet. I was able to add the day integer to the Day field but could not populate it with an actual date so I added the Date column to see if I could do an update. What I need to get is a datatype of date in the date column. I really don't need both columns but I could not get Access to add the date to anything. If I could get the day column (field) to read a date that would be fine also. I just need a date field.

I am beginning to think I may need to add a date column to the excel file but I did not want to do that. I could build a formula to add a date there Access is a little more complicated.

Thanks for any help you can offer.

[XLGibbs]

Easiest way would be to add the date column to Excel

You can use the DAY (from the sheet name) to build a date field easily enough using dateadd...and just add the days to the Month/0/Year you are looking to populate.

[GtrJim]

Thanks for you input. I was beginning to think I was going to have to add it to excel. I have kind of resorted to that process in my thinking over the paste few days. I did not want to do that but have already done it in another Excel worksheet so I may as well do it with this one.

Thanks for the help from everyone.