append queries and expressions used

Topic

New Reply

i am trying to update a column in a table, that is currently blank, with today date. i created an append query to try and populate the [table]![todaysdate] field with todays date. i selected the DATE() function as the “update to” value and the criteria value “is null”. when i try and run this it gives me the mesage “function isn’t available in expressions in query expression DATE()”. Can anyone tell me what i am missing here?

thanks
mike

Reply | Quote

Replies

If you are adding the date to existing records you need to use an update query. It works fine for me.

An append query will try to append new records. Each record would have only today’s date in it. Access might have problems knowing how many such records you want

Reply | Quote

I agree with grugeon.
Can you give more details on what you want to do?
Do you want to append records to your table or do you want to update existing records in your table?

If you get error messages please post the exact error message and the SQL that causes the error.

Reply | Quote

Sorry gents, it is an update query, i used the expression builder to create this:

IIf([MRComplete]![Todays Date] Is Null,Date())

i want to update a column in 34 rows that already exist. i want to only update when the value of the cell is null. i have ACCESS97 and am running on win95….this process will then be placed on a platform running WIN NT.

thanks

Reply | Quote

Hi,
All you need in your update query is the field you want to update, set the Update To to Date() and then enter Is Null in the criteria field. This will then update every one of the date fields that is null to today’s date.
Hope that helps.

Reply | Quote

i tried that and got the same messsage:

function isn’t available in expressions in query expression DATE()

for some reason it is like i can’t use this date() function in this manner, yet if i use NOW() it works fine except it give me the time as well.

mike

Reply | Quote

try now() – Time()
it has worked for me in the past
JerryC

Reply | Quote

i get the same message listing these two functions as not available.

thanks.

Reply | Quote

Mike,
Can you post the SQL from your query?

Reply | Quote

here it is(i also have some code to change the case for other fields):

UPDATE MRComplete SET MRComplete.[Todays Date] = Date(), MRComplete.Developer = IIf([MRComplete]![Developer] Is Null,”NOT ASSIGNED”,UCase([MRComplete]![Developer])), MRComplete.Creator = IIf([MRComplete]![Creator] Is Null,”NOT ASSIGNED”,UCase([MRComplete]![Creator])), MRComplete.System = UCase([MRComplete]![System])
WHERE (((MRComplete.[Todays Date]) Is Null));

Reply | Quote

Mike,
It sounds to me as though your database may have a missing reference (that tends to cause a lot of function errors).
If you press Alt-f11 and then choose Tools-References is there anything listed as Missing?

Reply | Quote

tried ALT F11 (nothing seemed to happen though) selected TOOLS and i have no selection called REFERENCE. At what point do i hit ALT F11, while in the tables tab or within the query itself?
thanks

Reply | Quote

try control G
then tools
then references
JerryC

Reply | Quote

hey one step closer, the only item listed as missing were
outlook 98 type library
microsoft scriptlet library

there are quite a lot of items not checked off either

Reply | Quote

If you uncheck those items, does the query then run?

Reply | Quote

YES!

Thanks again for everyone’s help. I got this site from another techie site i used to use and this one has been very helpful and the responses quick. I feel pretty confident about getting answers here. Everyone have a great day!

mike

Reply | Quote