passing text variable to SQL statement

  • Thread starter Thread starter Angi
  • Start date Start date
A

Angi

I know this is easy, but my brain is dead tonight and i can't figure this out.
I read the earlier post but it was for a date and i need the answer for text.
Since i can't figure out how to create a single query to do what i need, i'm
writing a module to do it for me. :) There will be approx. 20 statements and
they all need to be for the same month, but i can't get the sql to recognize
the InpMonth.

TIA!

Dim Message, Title, InpMonth
Message = "Enter a report date ie: JULY04" ' Set prompt.
Title = "Report Date" ' Set title.
' Display message, title, and default value.
InpMonth = InputBox(Message, Title)

DoCmd.RunSQL "UPDATE [Z_Monthly PRVU Report], CCH_PRO_RVU_FY05 SET
[Z_Monthly PRVU Report].TOT_Num =
(CCH_PRO_RVU_FY05!CCH_ER_CT)+(CCH_PRO_RVU_FY05!CCH_ER_FLUORO)+(CCH_PRO_RVU
_FY05!CCH_ER_MAMMO)+(CCH_PRO_RVU_FY05!CCH_ER_MRI) WHERE ((([Z_Monthly PRVU
Report].Place)='cch_er') AND ((CCH_PRO_RVU_FY05.MONTH)= InpMonth));"
 
Angi,

Queries don't recognise variables. You need to write a function that returns
the variable's value.

Public Function GetInpMonth() As Variant
GetInpMonth = InpMonth
End Function

Dim Message, Title, InpMonth
Message = "Enter a report date ie: JULY04" ' Set prompt.
Title = "Report Date" ' Set title.
' Display message, title, and default value.
InpMonth = InputBox(Message, Title)

DoCmd.RunSQL "UPDATE [Z_Monthly PRVU Report], CCH_PRO_RVU_FY05 SET
[Z_Monthly PRVU Report].TOT_Num =
(CCH_PRO_RVU_FY05!CCH_ER_CT)+(CCH_PRO_RVU_FY05!CCH_ER_FLUORO)+(CCH_PRO_RVU
_FY05!CCH_ER_MAMMO)+(CCH_PRO_RVU_FY05!CCH_ER_MRI) WHERE ((([Z_Monthly PRVU
Report].Place)='cch_er') AND ((CCH_PRO_RVU_FY05.MONTH)= GetInpMonth()));"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
I know this is easy, but my brain is dead tonight and i can't figure this out.
I read the earlier post but it was for a date and i need the answer for text.
Since i can't figure out how to create a single query to do what i need, i'm
writing a module to do it for me. :) There will be approx. 20 statements and
they all need to be for the same month, but i can't get the sql to recognize
the InpMonth.

TIA!

Dim Message, Title, InpMonth
Message = "Enter a report date ie: JULY04" ' Set prompt.
Title = "Report Date" ' Set title.
' Display message, title, and default value.
InpMonth = InputBox(Message, Title)

DoCmd.RunSQL "UPDATE [Z_Monthly PRVU Report], CCH_PRO_RVU_FY05 SET
[Z_Monthly PRVU Report].TOT_Num =
(CCH_PRO_RVU_FY05!CCH_ER_CT)+(CCH_PRO_RVU_FY05!CCH_ER_FLUORO)+(CCH_PRO_RVU
_FY05!CCH_ER_MAMMO)+(CCH_PRO_RVU_FY05!CCH_ER_MRI) WHERE ((([Z_Monthly PRVU
Report].Place)='cch_er') AND ((CCH_PRO_RVU_FY05.MONTH)= InpMonth));"
Click to expand...

You're inserting the *name* of the variable into the SQL string. You
need to insert its *value*, and you need to delimit it with
quotemarks:

DoCmd.RunSQL "UPDATE [Z_Monthly PRVU Report], CCH_PRO_RVU_FY05 " _
& "SET [Z_Monthly PRVU Report].TOT_Num = " _
& "(CCH_PRO_RVU_FY05!CCH_ER_CT) + (CCH_PRO_RVU_FY05!CCH_ER_FLUORO) " _
& " + (CCH_PRO_RVU_FY05!CCH_ER_MAMMO) + " _
& "(CCH_PRO_RVU_FY05!CCH_ER_MRI)" _
& " WHERE ((([Z_Monthly PRVU Report].Place)='cch_er')" _
& " AND ((CCH_PRO_RVU_FY05.MONTH)='" & InpMonth & "'));"

See my reply in the other newsgroup. I'm really queasy about your
table structure and this whole process!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Oops. I wasn't thinking about the fact that it was in VBA. John's suggestion
is the way to go. (It's been a hard day).

But just so you know, using a function is the only way to use a variable in
a saved query.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Graham R Seach said:
Angi,

Queries don't recognise variables. You need to write a function that
returns the variable's value.

Public Function GetInpMonth() As Variant
GetInpMonth = InpMonth
End Function

Dim Message, Title, InpMonth
Message = "Enter a report date ie: JULY04" ' Set prompt.
Title = "Report Date" ' Set title.
' Display message, title, and default value.
InpMonth = InputBox(Message, Title)

DoCmd.RunSQL "UPDATE [Z_Monthly PRVU Report], CCH_PRO_RVU_FY05 SET
[Z_Monthly PRVU Report].TOT_Num =
(CCH_PRO_RVU_FY05!CCH_ER_CT)+(CCH_PRO_RVU_FY05!CCH_ER_FLUORO)+(CCH_PRO_RVU
_FY05!CCH_ER_MAMMO)+(CCH_PRO_RVU_FY05!CCH_ER_MRI) WHERE ((([Z_Monthly PRVU
Report].Place)='cch_er') AND ((CCH_PRO_RVU_FY05.MONTH)= GetInpMonth()));"

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


Angi said:
I know this is easy, but my brain is dead tonight and i can't figure this
out.
I read the earlier post but it was for a date and i need the answer for
text.
Since i can't figure out how to create a single query to do what i need,
i'm
writing a module to do it for me. :) There will be approx. 20
statements and
they all need to be for the same month, but i can't get the sql to
recognize
the InpMonth.

TIA!

Dim Message, Title, InpMonth
Message = "Enter a report date ie: JULY04" ' Set prompt.
Title = "Report Date" ' Set title.
' Display message, title, and default value.
InpMonth = InputBox(Message, Title)

DoCmd.RunSQL "UPDATE [Z_Monthly PRVU Report], CCH_PRO_RVU_FY05 SET
[Z_Monthly PRVU Report].TOT_Num =
(CCH_PRO_RVU_FY05!CCH_ER_CT)+(CCH_PRO_RVU_FY05!CCH_ER_FLUORO)+(CCH_PRO_RVU
_FY05!CCH_ER_MAMMO)+(CCH_PRO_RVU_FY05!CCH_ER_MRI) WHERE ((([Z_Monthly
PRVU
Report].Place)='cch_er') AND ((CCH_PRO_RVU_FY05.MONTH)= InpMonth));"
Click to expand...
Click to expand...
 
John,
Thank you for that! it worked of course! As far as the table structure, I
hate it too!! My cousin "designed" it and it's awful as far as i'm concerned.
She needs this report by tomorrow, so I needed to do it before I started
re-creating the whole database.

Thanks again for all your help!
Angi
 
Back
Top