VB variable in SQL (1 Viewer)

[jesseb]

I would like to have a query run using a variable in the select part of the SQL. The variable allows the query to be dynamic. Here is where my code is at so far:
DoCmd.RunSQL "SELECT ExcelImportUnitsForecast.{put in variable}, [Price]*{variable name} AS Revenue" & _
"FROM ExcelImportUnitsForecast INNER JOIN test ON (ExcelImportUnitsForecast.SKU = test.SKU) AND ExcelImportUnitsForecast.Company = test.Company)"

How do I do this?

 

[Matthew Snook]

Jesse:
If instead of "{put in variable}" you wrote "[put in variable]" then Access would not recognize what's inside the brackets and would bring up a dialog box asking you to fill in the expression at runtime. The dialog box would say "put in variable?" In other words, whatever you put in the brackets that does not equate to a field becomes the prompt in the runtime dialog box.

Matt

 

[jesseb]

I haven't been able to get that to work. I would really like to use the variable HOLD in a way like this:
"Select ExcelImportUnits.[HOLD], ExcelImportUnits.SKU &_
"From ExcelImportUnitsForecast..."

Can I use a VB variable in SQL like that?

 

[raindrop3]

Hello,

1. You use the querie wizard.

In the criteria field of the relevant field type a reference to your field on a form. Like this: forms!frmYourForm!Yourfield.

The SQL looks like this:

SELECT tblnumber.subnumber
FROM tblnumber
WHERE (((tblnumber.subnumber)=[forms]![frmtest]![YourField]));


2. You write your querie in VBA

You can use a variable as following:

Assume 'Name' is a variable

name = "Customers"
"SELECT * from " & name & " Where [number] = '10a'")

Hope you can do something with this.

Albert


[This message has been edited by raindrop3 (edited 01-10-2002).]