Building an Expression for an Update Query

[Hasas]

I am new to access and need someone to help me build an expression. I need to
update the "expiry date" field in certain records so that when I run the
query it changes the "expiry date" to be two weeks before the beginning of
each month. Can anyone tell me how to do this?? I am using Access 2003.

 

[Douglas J. Steele]

Two weeks from the beginning of the month would be the 15th of the month,
wouldn't it?

DateSerial(Year(Date), Month(Date), 15)

will give you that date.

 

[fredg]

I am new to access and need someone to help me build an expression. I need to
update the "expiry date" field in certain records so that when I run the
query it changes the "expiry date" to be two weeks before the beginning of
each month. Can anyone tell me how to do this?? I am using Access 2003.

We can't see your database.
Of certain records?
What criteria determines which records to change?

I'll assume by "to be two weeks before the beginning of
each month" to mean the beginning of the month following the month
you run this query.

Update YourTable Set YourTable.[Expiry Date] =
DateSerial(Year(Date()),Month(Date())+1,1) -14
Where [SomeField] = Some Criteria

 

[Hasas]

Thank you for your response!
I was hoping for an expression that would give me two weeks before the
beginning of each month. But the expression you gave is still helpful. Thanks!

Hasas

 

[Douglas J. Steele]

Sorry, I misread.

Two weeks before the beginning of the month would be:

DateAdd("w", -2, DateSerial(Year(Date), Month(Date),1))

 

[Hasas]

Thank you for your response.

"What criteria determines which records to change?"
- Yes, only the records from a specific department will need this change to
be made on a monthly basis. Field is "Responsible Department"

"I'll assume by "to be two weeks before the beginning of
each month" to mean the beginning of the month following the month
you run this query."
- Correct

Thanks again!

I am new to access and need someone to help me build an expression. I need to
update the "expiry date" field in certain records so that when I run the
query it changes the "expiry date" to be two weeks before the beginning of
each month. Can anyone tell me how to do this?? I am using Access 2003.

We can't see your database.
Of certain records?
What criteria determines which records to change?

I'll assume by "to be two weeks before the beginning of
each month" to mean the beginning of the month following the month
you run this query.

Update YourTable Set YourTable.[Expiry Date] =
DateSerial(Year(Date()),Month(Date())+1,1) -14
Where [SomeField] = Some Criteria

 

[Hasas]

Thanks so much! Very helpful!

Hasas

Sorry, I misread.

Two weeks before the beginning of the month would be:

DateAdd("w", -2, DateSerial(Year(Date), Month(Date),1))

 

[Hasas]

When I try this it comes up "Data type mismatch in criteria expression". My
field data type is "short date", how can I modify the expression to work?

Thanks, Hasas

 

[Hasas]

I entered your expression. Now it says "Enter Parameter Value". What does
that mean?

Thank you for your help,
Hasas

I am new to access and need someone to help me build an expression. I need to
update the "expiry date" field in certain records so that when I run the
query it changes the "expiry date" to be two weeks before the beginning of
each month. Can anyone tell me how to do this?? I am using Access 2003.

We can't see your database.
Of certain records?
What criteria determines which records to change?

I'll assume by "to be two weeks before the beginning of
each month" to mean the beginning of the month following the month
you run this query.

Update YourTable Set YourTable.[Expiry Date] =
DateSerial(Year(Date()),Month(Date())+1,1) -14
Where [SomeField] = Some Criteria

 

[Douglas J. Steele]

What's the SQL of your query?

If you're not familiar with working directly with the SQL generated by
Access, open the query in Design view, then select SQL View from the View
menu.

 

[fredg]

I entered your expression. Now it says "Enter Parameter Value". What does
that mean?

Thank you for your help,
Hasas

I am new to access and need someone to help me build an expression. I need to
update the "expiry date" field in certain records so that when I run the
query it changes the "expiry date" to be two weeks before the beginning of
each month. Can anyone tell me how to do this?? I am using Access 2003.

We can't see your database.
Of certain records?
What criteria determines which records to change?

I'll assume by "to be two weeks before the beginning of
each month" to mean the beginning of the month following the month
you run this query.

Update YourTable Set YourTable.[Expiry Date] =
DateSerial(Year(Date()),Month(Date())+1,1) -14
Where [SomeField] = Some Criteria

It means Access cannot find whatever field you wrote. Either it was
miss-spelled or it doesn't exist in the table. It also asked for a
specific parameter value. What else did the "Enter Parameter Value"
dialog box ask for.

You need to post the exact query SQL.
Open the query in Design View. Click on View + SQL View.
Highlight the entire SQL. Copy it. Paste it into a reply message.

Also need to know the datatype of the "Responsible Department" field?
Is it Text or Number datatype?