sql to vb

[sobeit via AccessMonster.com]

hello

i would like to ask for help for the vb conversion of this sql
please

SELECT MONITORING.SO, MONITORING.PROJECT, MONITORING.QTY, MONITORING.CODE,
MONITORING.SOTYPE, MONITORING.REMARKS
FROM MONITORING
WHERE (((MONITORING.SO)="09-039") AND ((MONITORING.SOTYPE)=1 And (MONITORING.
SOTYPE)=2));


thanks in advance

 

[ErezM via AccessMonster.com]

hi
vb is an event driven com programming lanuage and cannot "speak" to databases
in any other language than sql, so the answer is: the code is the same, the
question is, what do you need vb to do?

 

[Stuart McCall]

hello

i would like to ask for help for the vb conversion of this sql
please

SELECT MONITORING.SO, MONITORING.PROJECT, MONITORING.QTY, MONITORING.CODE,
MONITORING.SOTYPE, MONITORING.REMARKS
FROM MONITORING
WHERE (((MONITORING.SO)="09-039") AND ((MONITORING.SOTYPE)=1 And
(MONITORING.
SOTYPE)=2));


thanks in advance

Dim s As String

s = "SELECT MONITORING.SO,"
s = s & "MONITORING.PROJECT,"
s = s & "MONITORING.QTY,"
s = s & "MONITORING.CODE,"
s = s & " MONITORING.SOTYPE,"
s = s & "MONITORING.REMARKS"
s = s & " FROM MONITORING"
s = s & " WHERE (((MONITORING.SO)=""09-039"")"
s = s & " AND ((MONITORING.SOTYPE)=1"
s = s & " AND (MONITORING.SOTYPE)=2));"

This code was generated by my addin 'SQL Formatter', which you can find
here:

http://www.smccall.demon.co.uk/Downloads.htm#SQLFormat

 

[Albert D. Kallal]

It not clear in what context you want to use this sql.

You have to be "doing something" with the sql in code. You can't just run
some sql. Is the output to go to a report, the screen, a table, a file on
the disk.....

So, you can't just throw some sql up in the air and hope it lands somewhere.

However, you can pull use vb to pull that sql data in memory and process it
with vb. the code would be:


dim strSql as string
dim rstData as dao.RecordSet

strSql = "SELECT SO, PROJECT, QTY, CODE,SOTYPE, REMARKS " & _
"FROM MONITORING " & _
"WHERE SO = '09-039' and SOTYPE =1 And SOTYPE = 2"

set rstData = currentdb.OpenRecordSet(strSql)

do while rstData.EOF = false
debug.print rstData!Project
rstData.movenext
loop
rstData.Close

The above would loop in code and process records one by one.. What you do
inside of that loop is up to you.

So, you can't "convert" the sql to vba, but you can most certainly use sql
in code to pull data from tables and process that data.

 

[sobeit via AccessMonster.com]

thank you albert only the way i needed it

It not clear in what context you want to use this sql.

You have to be "doing something" with the sql in code. You can't just run
some sql. Is the output to go to a report, the screen, a table, a file on
the disk.....

So, you can't just throw some sql up in the air and hope it lands somewhere.

However, you can pull use vb to pull that sql data in memory and process it
with vb. the code would be:

dim strSql as string
dim rstData as dao.RecordSet

strSql = "SELECT SO, PROJECT, QTY, CODE,SOTYPE, REMARKS " & _
"FROM MONITORING " & _
"WHERE SO = '09-039' and SOTYPE =1 And SOTYPE = 2"

set rstData = currentdb.OpenRecordSet(strSql)

do while rstData.EOF = false
debug.print rstData!Project
rstData.movenext
loop
rstData.Close

The above would loop in code and process records one by one.. What you do
inside of that loop is up to you.

So, you can't "convert" the sql to vba, but you can most certainly use sql
in code to pull data from tables and process that data.

 

[sobeit via AccessMonster.com]

my exact code is this

sitemSource = "SELECT [MONITORING].[SO],[MONITORING].

,[MONITORING].[QTY]
,[MONITORING].UNIT,[MONITORING].[PROJECT] " & _
"FROM MONITORING " & _
"WHERE [SO] = """ & Forms![WITHDRAW]!cboSONUMBER &
""""


and i want to filter the [monitoring].[code] to not null or no blank values
how will i add it to this existing codes

please help

 

[Albert D. Kallal]

my exact code is this

sitemSource = "SELECT
[MONITORING].[SO],[MONITORING].

,[MONITORING].[QTY]
,[MONITORING].UNIT,[MONITORING].[PROJECT] " & _
"FROM MONITORING " & _
"WHERE [SO] = """ & Forms![WITHDRAW]!cboSONUMBER &
""""[/QUOTE]

try:

sitemSource = " select so,code,qty,Unit,Project " & _
" from MONITORING" & _
" where os = '" & me.cboSONUMBER & "'" & _
" and code is not null"

Note that if OS is a number collum, and not text, then use:

"WHERE OS = " & me.cboSONUMBER & _

(so no need for quotes if os is a number field)

 

[sobeit via AccessMonster.com]

as u instructed iv used the ff code

sitemSource = "SELECT [MONITORING].[SO],[MONITORING].

,[MONITORING].[QTY]
,[MONITORING].UNIT,[MONITORING].[PROJECT] " & _
"WHERE [SO] = """ & Forms![WITHDRAW]!cboSONUMBER &
"""" & _
[CODE] Is Not Null

but still the error occurs :(
the cboSONUMBER field is text

thanks albert

[QUOTE][QUOTE]
my exact code is this
[/QUOTE]
[quoted text clipped - 4 lines][QUOTE]
"WHERE [SO] = """ & Forms![WITHDRAW]!cboSONUMBER &
""""[/QUOTE]

try:

sitemSource = " select so,code,qty,Unit,Project " & _
" from MONITORING" & _
" where os = '" & me.cboSONUMBER & "'" & _
" and code is not null"

Note that if OS is a number collum, and not text, then use:

"WHERE OS = " & me.cboSONUMBER & _

(so no need for quotes if os is a number field)
[/QUOTE]

 

[Douglas J. Steele]

You're missing the keyword AND in your query, plus the

 Is Not Null
needs to be inside the quotes:

sitemSource = "SELECT [MONITORING].[SO],[MONITORING].[CODE], " & _
"[MONITORING].[QTY],[MONITORING].UNIT,[MONITORING].[PROJECT] " & _
"WHERE [SO] = """ & Forms![WITHDRAW]!cboSONUMBER &  """" & _
" AND [CODE] Is Not Null"


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


[QUOTE="sobeit via AccessMonster.com"]
as u instructed iv used the ff code

sitemSource = "SELECT
[MONITORING].[SO],[MONITORING].[CODE],[MONITORING].[QTY]
,[MONITORING].UNIT,[MONITORING].[PROJECT] " & _
"WHERE [SO] = """ & Forms![WITHDRAW]!cboSONUMBER &
"""" & _
[CODE] Is Not Null

but still the error occurs :(
the cboSONUMBER field is text

thanks albert

[QUOTE][QUOTE]
my exact code is this
[/QUOTE]
[quoted text clipped - 4 lines][QUOTE]
"WHERE [SO] = """ & Forms![WITHDRAW]!cboSONUMBER
&
""""[/QUOTE]

try:

sitemSource = " select so,code,qty,Unit,Project " & _
" from MONITORING" & _
" where os = '" & me.cboSONUMBER & "'" & _
" and code is not null"

Note that if OS is a number collum, and not text, then use:

"WHERE OS = " & me.cboSONUMBER & _

(so no need for quotes if os is a number field)
[/QUOTE]

--
" So be it"


http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200907/1
[/QUOTE]

 

[sobeit via AccessMonster.com]

thanks mr. douglas

You're missing the keyword AND in your query, plus the

 Is Not Null
needs to be inside the quotes:

sitemSource = "SELECT [MONITORING].[SO],[MONITORING].[CODE], " & _
"[MONITORING].[QTY],[MONITORING].UNIT,[MONITORING].[PROJECT] " & _
"WHERE [SO] = """ & Forms![WITHDRAW]!cboSONUMBER &  """" & _
" AND [CODE] Is Not Null"
[QUOTE]
as u instructed iv used the ff code
[/QUOTE]
[quoted text clipped - 29 lines][QUOTE][/QUOTE][/QUOTE]