RunSQL Action Query Error

[OxDavis]

I am attempting to have an action query populate the "Age" field of my demographics table. I want my input form to run the action query after the DOB field has been updated as to populate that field as the form is being completed. However, I continue to get "Compile Error: Argument Not Optional" I know my error must be simple, but I, being the tyro that I am, am clueless. Here is my code:

Private Sub DOB_AfterUpdate()
DoCmd.RunSQL 'UPDATE tblDemographics SET tblDemographics.Age = DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")))'
End Sub

My "MySQL" command is all on one line and appears to be in the correct syntax. The query runs perfectly outside of its attachment to the form.

Thanks in advance.

 

[izyrider]

parenthesis count (i hope you understand my "syntax"):



DateDiff("yyyy",[DOB],Now())
0000000011111111111111111210

+Int(Format(Now(),"mmdd")<Format
00001111111222232222222211111111

([DOB],"mmdd")))'
222222222222210x



izy

 

[OxDavis]

Thanks for the prompt reply, I tried taking one of the close parens off, but to no avail.

Private Sub DOB_AfterUpdate()
DoCmd.RunSQL 'UPDATE tblDemographics SET tblDemographics.Age = DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))'
End Sub

Same error with the "Argument Not Optional". Any suggestions?

 

[OxDavis]

You're not going to believe this, but I swapped out the single quotes on the outside with double quotes and put the single quotes inside the statement and now it works Thanks for the help guys.

 

[izyrider]

if you're happy, i'm happy.
no way it runs as posted with only the " for ' swap tho.

izy

 

[OxDavis]

Even if I took the close paren off as you suggested? I followed your advice but it still wouldn't run, then I combined your suggestion with the flip flop of quotations and it ran. Is there another issue with the statement?

 

[Bat17]

Real question is why do it? if you are storing DOB why not create the age on the fly when you need it. The way you have the SQL I think that you will update every record each time you add a new one.

Peter

 

[OxDavis]

I need the Age field populated in the tblDemographics so that users can search by age. Are you saying that I might create these ages as a precursor to searching for them? Please amplify your suggestion, I am open to anything that makes the DB more robust and user friendly.

 

[Bat17]

you can create an 'Age' column in a query using your formula.
AgeateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

or you could add a reference to a form to get ages at a specific date
AgeateDiff("yyyy",[Dob],[Forms]![MyForm]![MyDate])+Int(Format([Forms]![MyForm]![MyDate],"mmdd")<Format([Dob],"mmdd"))

Peter

 

[izyrider]

unless i'm missing the plot somewhere, you are trying to construct a string that is going to work with
docmd.runsql "your string goes here"

your posted code simply should not work!
if you do the "swap' and fix the leftover ) it should work in SQL-view of query analyser, but it certainly shouldn't work in .execute (...or, AFAIK in .runsql)

your sql includes the literal characters '[' and 'D' and 'O' and 'B' and ']'
sadly, A's query SQL-view usually makes sense out of this spaghetti, but it really should not work in code.

you should be concatenating in the value held in the txtbox or field named 'DOB'. something like "sqlsqlsql" & DOB & "sqlsqlsql".

(
OK - it is true that i don't use
docmd.runsql "sqlsqlsql"
because
currentdb.execute "sqlsqlsql
is
a/ faster
b/ doesn't require setwarnings to block mama-Gates' message
c/ works in DAO & ADO since all Access forms are inherently DAO whether we like it or not
so maybe .runsql handles spaghetti - i don't know
)

now moving to the actual calc you are doing ("concatenated" your way)
Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))
this is too clever: try the same expression in SQL-Server!
(don't get me wrong - it is a really really cute trick, and i will play with it for my private A apps)

izy