How to get the content of a variable an a select statment

[mieke]

Hi,

I calculated the where clause of mij select statment in a variable,
how can I get the content of the variable in my select statment.

vb
if my variable condition contains "Model like txtModel and status like
txtStatus"

is there a way to past this beween a select statement?

 

[Dirk Goldgar]

Hi,

I calculated the where clause of mij select statment in a variable,
how can I get the content of the variable in my select statment.

vb
if my variable condition contains "Model like txtModel and status like
txtStatus"

is there a way to past this beween a select statement?

Only by building the whole SELECT statement in code. If you'll give a
little background on how you're trying to use this technique, we could
probably suggest how to go about it.

 

[OldPro]

Hi,

I calculated the where clause of mij select statment in a variable,
how can I get the content of the variable in my select statment.

vb
if my variable condition contains "Model like txtModel and status like
txtStatus"

is there a way to past this beween a select statement?

Sure. Here is some sample code to fill a listbox with records from
two joined tables using user-supplied dates:

Dim sSELECT as string
Dim sWHERE as string
sSELECT ="SELECT [LName],[FName],[InvoiceNumber],[InvoiceDate],[Total]
FROM tblCustomers INNER JOIN tblSales ON tblCustomer.[CustomerNumber]
= tblSales.[CustomerNumber] "
sWHERE = "WHERE [InvoiceDate] >= #" & dInvoiceDateStart & "# AND
[InvoiceDate] <= #" & dInvoiceDateEnd & "#;"

ListBox1.RowSource = sSELECT & sWHERE

 

[mieke]

Hi,

I calculated the where clause of mij select statment in a variable,
how can I get the content of the variable in my select statment.

vb
if my variable condition contains "Model like txtModel and status like
txtStatus"

is there a way to past this beween a select statement?

Sure. Here is some sample code to fill a listbox with records from
two joined tables using user-supplied dates:

Dim sSELECT as string
Dim sWHERE as string
sSELECT ="SELECT [LName],[FName],[InvoiceNumber],[InvoiceDate],[Total]
FROM tblCustomers INNER JOIN tblSales ON tblCustomer.[CustomerNumber]
= tblSales.[CustomerNumber] "
sWHERE = "WHERE [InvoiceDate] >= #" & dInvoiceDateStart & "# AND
[InvoiceDate] <= #" & dInvoiceDateEnd & "#;"

ListBox1.RowSource = sSELECT & sWHERE

Hi,

thank you for the answers already, but I wanted to use this select
statement to open a recordset.

this is what I already have on code

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox
Then
If Not IsNull(ctl.Value) Then
qry = qry + ctl.Name + ","
teller = teller + 1
End If
End If
Next
MsgBox teller
MsgBox qry

' Determine where condition
lengte = Len(qry)
For i = 1 To lengte
If Mid(qry, i, 1) <> "," Then
fieldtxt = fieldtxt + Mid(qry, i, 1)
Else
fieldtxtlen = Len(fieldtxt)
field = Mid(fieldtxt, 4, fieldtxtlen - 3)
Condition = Condition + " and " + field + " like " +
fieldtxt + ".value "
fieldtxt = ""
End If
Next
MsgBox Condition

rstado.Open "select tblMaterial.AssetTag,
tblMaterial.SerialNumber, tblMaterial.Model, tblMaterial.Status, " & _
"tblUsers.LastName, tblUsers.FirstName,
tblMaterial.OfficeLocation, tblUsers.CostCenter " & _
"from tblMaterial, tblUsers " & _
"where tblMaterial.accountName = tblUsers.accountName"
& Condition, CurrentProject.Connection, adOpenStatic, adLockReadOnly

but tihs gives me the error -214721794 No value given for required
parameter.

any idea?

 

[mieke]

Hi,

I calculated the where clause of mij select statment in a variable,
how can I get the content of the variable in my select statment.

vb
if my variable condition contains "Model like txtModel and status like
txtStatus"

is there a way to past this beween a select statement?

Sure. Here is some sample code to fill a listbox with records from
two joined tables using user-supplied dates:

Dim sSELECT as string
Dim sWHERE as string
sSELECT ="SELECT [LName],[FName],[InvoiceNumber],[InvoiceDate],[Total]
FROM tblCustomers INNER JOIN tblSales ON tblCustomer.[CustomerNumber]
= tblSales.[CustomerNumber] "
sWHERE = "WHERE [InvoiceDate] >= #" & dInvoiceDateStart & "# AND
[InvoiceDate] <= #" & dInvoiceDateEnd & "#;"

ListBox1.RowSource = sSELECT & sWHERE

Hi,

I tried it your way, but the only thing I get in my listbox is the
select statement, not the outcome of my select statement.

Thank you already,
Mieke

 

[OldPro]

Sure. Here is some sample code to fill a listbox with records from
two joined tables using user-supplied dates:

Dim sSELECT as string
Dim sWHERE as string
sSELECT ="SELECT [LName],[FName],[InvoiceNumber],[InvoiceDate],[Total]
FROM tblCustomers INNER JOIN tblSales ON tblCustomer.[CustomerNumber]
= tblSales.[CustomerNumber] "
sWHERE = "WHERE [InvoiceDate] >= #" & dInvoiceDateStart & "# AND
[InvoiceDate] <= #" & dInvoiceDateEnd & "#;"

ListBox1.RowSource = sSELECT & sWHERE

Hi,

I tried it your way, but the only thing I get in my listbox is the
select statement, not the outcome of my select statement.

Thank you already,
Mieke- Hide quoted text -

- Show quoted text -

The code in the example is good. It sounds like you need more
experience debugging. Judging by your example, you are using ADO. Is
this correct? Is your system setup to use DAO too? What version of
Access are you using?
When you tried my example, did you set the RowSourceType of the
listbox to Table/Query?
The easiest way to debug a SQL query is to enter it in the SQL view of
the query builder. To do this, click on the Query tab of the database
dialog, and select New. Then click Design View. Select the
appropriate table from the list, and click Add. Then click Close to
exit the table picker. Select View - > SQLView from the menu.
Replace the existing SQL query with the one you want to test. Then
select "View -> Datasheet View" from the menu. If there is an error,
the point where the error occurs will be highlighted. If no error is
encountered, you will see the resultant recordset. If the returned
recordset does not match what you expected, then you know to change
your SQL statement.
If you are building a SQL string in a program, then attach a code
break (Debug -> Toggle Break Point) to the line after you assign the
final portion of your SQL clause to the SQL string variable. Run the
code: it will stop at this line. Then bring up the debug window(Crtl-
G) and display the contents of the SQL variable with a print statement
(the question mark followed by the variable name will do).
After you hit enter, the contents of the variable will display in the
debug window. Finally, highlight the entire SQL statement (it should
be on a line by itself) and do a Ctrl-Insert. Then follow the
instructions above to create a new query, and open the SQL View. Do a
Shift - Insert to paste the SQL clause from the debug window to the
SQL View.
Like all things, it becomes easier with practice.

 

[mieke]

Hi,
I calculated the where clause of mij select statment in a variable,
how can I get the content of the variable in my select statment.
vb
if my variable condition contains "Model like txtModel and status like
txtStatus"
is there a way to past this beween a select statement?
Sure. Here is some sample code to fill a listbox with records from
two joined tables using user-supplied dates:
Dim sSELECT as string
Dim sWHERE as string
sSELECT ="SELECT [LName],[FName],[InvoiceNumber],[InvoiceDate],[Total]
FROM tblCustomers INNER JOIN tblSales ON tblCustomer.[CustomerNumber]
= tblSales.[CustomerNumber] "
sWHERE = "WHERE [InvoiceDate] >= #" & dInvoiceDateStart & "# AND
[InvoiceDate] <= #" & dInvoiceDateEnd & "#;"
ListBox1.RowSource = sSELECT & sWHERE

Hi,

I tried it your way, but the only thing I get in my listbox is the
select statement, not the outcome of my select statement.

Thank you already,
Mieke- Hide quoted text -

- Show quoted text -

The code in the example is good. It sounds like you need more
experience debugging. Judging by your example, you are using ADO. Is
this correct? Is your system setup to use DAO too? What version of
Access are you using?
When you tried my example, did you set the RowSourceType of the
listbox to Table/Query?
The easiest way to debug a SQL query is to enter it in the SQL view of
the query builder. To do this, click on the Query tab of the database
dialog, and select New. Then click Design View. Select the
appropriate table from the list, and click Add. Then click Close to
exit the table picker. Select View - > SQLView from the menu.
Replace the existing SQL query with the one you want to test. Then
select "View -> Datasheet View" from the menu. If there is an error,
the point where the error occurs will be highlighted. If no error is
encountered, you will see the resultant recordset. If the returned
recordset does not match what you expected, then you know to change
your SQL statement.
If you are building a SQL string in a program, then attach a code
break (Debug -> Toggle Break Point) to the line after you assign the
final portion of your SQL clause to the SQL string variable. Run the
code: it will stop at this line. Then bring up the debug window(Crtl-
G) and display the contents of the SQL variable with a print statement
(the question mark followed by the variable name will do).
After you hit enter, the contents of the variable will display in the
debug window. Finally, highlight the entire SQL statement (it should
be on a line by itself) and do a Ctrl-Insert. Then follow the
instructions above to create a new query, and open the SQL View. Do a
Shift - Insert to paste the SQL clause from the debug window to the
SQL View.
Like all things, it becomes easier with practice.- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi,

It is a fact that I need more experience in debugging, but this is the
first time that I make something like this in Access. I'm indeed
using ADO because My program will be used only with version 2003 or
higher.
Yes, I did put the rowsourceType to 'Table/query' in the mean time,
but it still doesn't give me the output I hoped for.

This would be my sql statement when I only fill in the class:


select tblMaterial.AssetTag,
tblmaterial.ClassID,tblMaterial.SerialNumber, tblMaterial.Model,
tblMaterial.Status, tblUsers.LastName, tblUsers.FirstName,
tblMaterial.OfficeLocation,tblUsers.CostCenter from tblMaterial inner
join tblUsers on tblMaterial.accountname = tblUsers.accountname where
tblmaterial.ClassID = me.cmbClassID.value

I think the problem has to do with the fact that he doesn't take the
content of my fields.

and indeed it wil become easier with practice

 

[OldPro]

Hi,
I calculated the where clause of mij select statment in a variable,
how can I get the content of the variable in my select statment.
vb
if my variable condition contains "Model like txtModel and status like
txtStatus"
is there a way to past this beween a select statement?
Sure. Here is some sample code to fill a listbox with records from
two joined tables using user-supplied dates:
Dim sSELECT as string
Dim sWHERE as string
sSELECT ="SELECT [LName],[FName],[InvoiceNumber],[InvoiceDate],[Total]
FROM tblCustomers INNER JOIN tblSales ON tblCustomer.[CustomerNumber]
= tblSales.[CustomerNumber] "
sWHERE = "WHERE [InvoiceDate] >= #" & dInvoiceDateStart & "# AND
[InvoiceDate] <= #" & dInvoiceDateEnd & "#;"
ListBox1.RowSource = sSELECT & sWHERE
Hi,
I tried it your way, but the only thing I get in my listbox is the
select statement, not the outcome of my select statement.
Thank you already,
Mieke- Hide quoted text -
- Show quoted text -

The code in the example is good. It sounds like you need more
experience debugging. Judging by your example, you are using ADO. Is
this correct? Is your system setup to use DAO too? What version of
Access are you using?
When you tried my example, did you set the RowSourceType of the
listbox to Table/Query?
The easiest way to debug a SQL query is to enter it in the SQL view of
the query builder. To do this, click on the Query tab of the database
dialog, and select New. Then click Design View. Select the
appropriate table from the list, and click Add. Then click Close to
exit the table picker. Select View - > SQLView from the menu.
Replace the existing SQL query with the one you want to test. Then
select "View -> Datasheet View" from the menu. If there is an error,
the point where the error occurs will be highlighted. If no error is
encountered, you will see the resultant recordset. If the returned
recordset does not match what you expected, then you know to change
your SQL statement.
If you are building a SQL string in a program, then attach a code
break (Debug -> Toggle Break Point) to the line after you assign the
final portion of your SQL clause to the SQL string variable. Run the
code: it will stop at this line. Then bring up the debug window(Crtl-
G) and display the contents of the SQL variable with a print statement
(the question mark followed by the variable name will do).
After you hit enter, the contents of the variable will display in the
debug window. Finally, highlight the entire SQL statement (it should
be on a line by itself) and do a Ctrl-Insert. Then follow the
instructions above to create a new query, and open the SQL View. Do a
Shift - Insert to paste the SQL clause from the debug window to the
SQL View.
Like all things, it becomes easier with practice.- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi,

It is a fact that I need more experience in debugging, but this is the
first time that I make something like this in Access. I'm indeed
using ADO because My program will be used only with version 2003 or
higher.
Yes, I did put the rowsourceType to 'Table/query' in the mean time,
but it still doesn't give me the output I hoped for.

This would be my sql statement when I only fill in the class:

select tblMaterial.AssetTag,
tblmaterial.ClassID,tblMaterial.SerialNumber, tblMaterial.Model,
tblMaterial.Status, tblUsers.LastName, tblUsers.FirstName,
tblMaterial.OfficeLocation,tblUsers.CostCenter from tblMaterial inner
join tblUsers on tblMaterial.accountname = tblUsers.accountname where
tblmaterial.ClassID = me.cmbClassID.value

I think the problem has to do with the fact that he doesn't take the
content of my fields.

and indeed it wil become easier with practice - Hide quoted text -

- Show quoted text -

1) This statment is all on one line, right?
2) tblMaterial.ClassID is a number, right?
If tblMaterial.ClassID is not a numeric data type, then put single
quotes around the cmbClassID, like so:
"...tblMaterial.ClassID = ' " & cmbClassID & " ' "
Otherwise, enclose cmbClassID in a Val() function.
"...tblMaterial.ClassID = " & val(cmbClassID)
If you don't use the ".value", then you don't need to set the focus.
3) It doesn't really say cmbClassID, when you do the aforementioned
debug procedure, right? It should fill it in with the contents of
cmbClassID.

 

[mieke]

Hi,
I calculated the where clause of mij select statment in a variable,
how can I get the content of the variable in my select statment.
vb
if my variable condition contains "Model like txtModel and status like
txtStatus"
is there a way to past this beween a select statement?
Sure. Here is some sample code to fill a listbox with records from
two joined tables using user-supplied dates:
Dim sSELECT as string
Dim sWHERE as string
sSELECT ="SELECT [LName],[FName],[InvoiceNumber],[InvoiceDate],[Total]
FROM tblCustomers INNER JOIN tblSales ON tblCustomer.[CustomerNumber]
= tblSales.[CustomerNumber] "
sWHERE = "WHERE [InvoiceDate] >= #" & dInvoiceDateStart & "# AND
[InvoiceDate] <= #" & dInvoiceDateEnd & "#;"
ListBox1.RowSource = sSELECT & sWHERE
Hi,
I tried it your way, but the only thing I get in my listbox is the
select statement, not the outcome of my select statement.
Thank you already,
Mieke- Hide quoted text -
- Show quoted text -
The code in the example is good. It sounds like you need more
experience debugging. Judging by your example, you are using ADO. Is
this correct? Is your system setup to use DAO too? What version of
Access are you using?
When you tried my example, did you set the RowSourceType of the
listbox to Table/Query?
The easiest way to debug a SQL query is to enter it in the SQL view of
the query builder. To do this, click on the Query tab of the database
dialog, and select New. Then click Design View. Select the
appropriate table from the list, and click Add. Then click Close to
exit the table picker. Select View - > SQLView from the menu.
Replace the existing SQL query with the one you want to test. Then
select "View -> Datasheet View" from the menu. If there is an error,
the point where the error occurs will be highlighted. If no error is
encountered, you will see the resultant recordset. If the returned
recordset does not match what you expected, then you know to change
your SQL statement.
If you are building a SQL string in a program, then attach a code
break (Debug -> Toggle Break Point) to the line after you assign the
final portion of your SQL clause to the SQL string variable. Run the
code: it will stop at this line. Then bring up the debug window(Crtl-
G) and display the contents of the SQL variable with a print statement
(the question mark followed by the variable name will do).
After you hit enter, the contents of the variable will display in the
debug window. Finally, highlight the entire SQL statement (it should
be on a line by itself) and do a Ctrl-Insert. Then follow the
instructions above to create a new query, and open the SQL View. Do a
Shift - Insert to paste the SQL clause from the debug window to the
SQL View.
Like all things, it becomes easier with practice.- Tekst uit oorspronkelijk bericht niet weergeven -
- Tekst uit oorspronkelijk bericht weergeven -

Hi,

It is a fact that I need more experience in debugging, but this is the
first time that I make something like this in Access. I'm indeed
using ADO because My program will be used only with version 2003 or
higher.
Yes, I did put the rowsourceType to 'Table/query' in the mean time,
but it still doesn't give me the output I hoped for.

This would be my sql statement when I only fill in the class:

select tblMaterial.AssetTag,
tblmaterial.ClassID,tblMaterial.SerialNumber, tblMaterial.Model,
tblMaterial.Status, tblUsers.LastName, tblUsers.FirstName,
tblMaterial.OfficeLocation,tblUsers.CostCenter from tblMaterial inner
join tblUsers on tblMaterial.accountname = tblUsers.accountname where
tblmaterial.ClassID = me.cmbClassID.value

I think the problem has to do with the fact that he doesn't take the
content of my fields.

and indeed it wil become easier with practice - Hide quoted text -

- Show quoted text -

1) This statment is all on one line, right?
2) tblMaterial.ClassID is a number, right?
If tblMaterial.ClassID is not a numeric data type, then put single
quotes around the cmbClassID, like so:
"...tblMaterial.ClassID = ' " & cmbClassID & " ' "
Otherwise, enclose cmbClassID in a Val() function.
"...tblMaterial.ClassID = " & val(cmbClassID)
If you don't use the ".value", then you don't need to set the focus.
3) It doesn't really say cmbClassID, when you do the aforementioned
debug procedure, right? It should fill it in with the contents of
cmbClassID.- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi,

1) the statement you see is in fact the content of a variable, so yes
it is on one line.
2) tblMaterial.classID is indeed a number

I brainstormed another time and slept over it and found a solution for
my problem.

what i do now is, I check wheter it is a number and put single quotes
around when it is tekst.
here's my working code

For i = 1 To lengte
If Mid(qry, i, 1) <> "," Then
fieldtxt = fieldtxt + Mid(qry, i, 1)
Else
fieldtxtlen = Len(fieldtxt)
field = Mid(fieldtxt, 4, fieldtxtlen - 3)

'set the active control to the current field so that I can get
the value of that field in the where clause
DoCmd.GoToControl (fieldtxt)
If IsNumeric(Me.ActiveControl.Value) Then
Condition = Condition + "tblmaterial." + field + " =
" + Me.ActiveControl.Value + _
" and "
Else
Condition = Condition + "tblmaterial." + field + " =
'" + Me.ActiveControl.Value + _
"' and "
End If
fieldtxt = ""
End If
Next

thank you very much for your help

Mieke