If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Pass variable to SQL

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2004
Location: California
Posts: 431
Pass variable to SQL

Okay - I'm not very good at explaining my problems but I'll do the best I can.

I have 20 small tables with one field containing approximately twenty office names.

I need Access VBA code to decalre a variable, then something like, for i from 1 to endOfTable, look at tblName, assign the first office name to the variable then run the SQL code below using the variable.

Then I will dump the WST and the WSTDV and the variable into a small table that I will place on the last page of a report and look for the next office in the tblName and do it again until all of the offices have been run.

Whew! Hope I got that right.

Please remember - I know nothing about VBA!


SQL:

SELECT Sum([SALESPRICE]) AS [WSTDV], Count(OCMonthlyData.SalesPrice) AS [WSTCount]
FROM OCMonthlyData
WHERE (((OCMonthlyData.OFFICELIST_OFFICENAME) Like "variable") AND ((OCMonthlyData.OFFICESELL_OFFICENAME) Not Like "variable"));

Thanks Much . . .

Rick ~ Access Newbie
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jul 2004
Location: Edmonton, Canada
Posts: 72
I assume OFFICENAME is a string, therefore:

sqlString = "SELECT BlabBlabBlab "
sqlString = sqlString & "WHERE OFFICENAME LIKE '" & myVar & "*' AND OTHEROFFICENAME NOT LIKE '" & myVar & "*'"
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2004
Location: California
Posts: 431
BlaBlaBla . . ?

Carl . . . not sure what blablabla is. Fieldname or table name?
sqlString = "SELECT BlabBlabBlab "

Can you help me with another definition? SELECT ~ how does this work in Access 2003?

sqlString = sqlString & "WHERE OFFICENAME LIKE '" & myVar & "*' AND OTHEROFFICENAME NOT LIKE '" & myVar & "*'"

Does this section go into the Criteria row?

Is it best to declare my Variable? Dim MyVariable AS string?

How would I then assign MyVariable to the fields in another table?

Thanks much . . .
Reply With Quote
  #4 (permalink)  
Old
Purveyor of Discontent
 
Join Date: Mar 2003
Location: The Bottom of The Barrel
Posts: 6,102
Let's put the code away for a minute. What are you trying to do here, in plain english?
__________________
oh yeah... documentation... I have heard of that.

*** What Do You Want In The MS Access Forum? ***
Reply With Quote
  #5 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,655
Quote:
Originally Posted by Rick Schreiber
...I have 20 small tables with one field containing approximately twenty office names....
20 small tables containing one field doesn't sound right, wonder if we should be pointing you to rudys site (r937.com/relational.htm)?
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jul 2004
Location: Edmonton, Canada
Posts: 72
Sorry, that would be select fields from tablename
Reply With Quote
  #7 (permalink)  
Old
Cavalier King Charles
 
Join Date: Dec 2002
Location: Préverenges, Switzerland
Posts: 3,739
partageons-nous la même définition de « anglais simple »?

izy
__________________
currently using SS 2008R2
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On