Access queries/ Search criteria

  • Thread starter Thread starter RivaCom
  • Start date Start date
R

RivaCom

I am using a Access Database to search/add a bunch of information in
MS VB.net 2005 express. In the query (querybuilder) everything seems
right, but in the WHERE statement I use [Number] = me.textbox.text .

Then in the statement I have an if statement

IF combobox.text = option1 Then
e.databaseadapter.GetDataBy2() 'which is the query i built
end if

I get an error each time, and can't figure out if it is with the sql
statement, or the execution. Is there a search critria field i need
to put in place of me.textbox.text?
 
On 18 Apr 2007 05:17:50 -0700, (e-mail address removed) wrote:

¤ I am using a Access Database to search/add a bunch of information in
¤ MS VB.net 2005 express. In the query (querybuilder) everything seems
¤ right, but in the WHERE statement I use [Number] = me.textbox.text .
¤
¤ Then in the statement I have an if statement
¤
¤ IF combobox.text = option1 Then
¤ e.databaseadapter.GetDataBy2() 'which is the query i built
¤ end if
¤
¤ I get an error each time, and can't figure out if it is with the sql
¤ statement, or the execution. Is there a search critria field i need
¤ to put in place of me.textbox.text?

What is the error that is being generated? BTW, Number is a Jet reserved word so it may not be a
good choice for a column name.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
On 18 Apr 2007 05:17:50 -0700, (e-mail address removed) wrote:

¤ I am using a Access Database to search/add a bunch of information in
¤ MS VB.net 2005 express. In the query (querybuilder) everything seems
¤ right, but in the WHERE statement I use [Number] = me.textbox.text .
¤
¤ Then in the statement I have an if statement
¤
¤ IF combobox.text = option1 Then
¤ e.databaseadapter.GetDataBy2() 'which is the query i built
¤ end if
¤
¤ I get an error each time, and can't figure out if it is with the sql
¤ statement, or the execution. Is there a search critria field i need
¤ to put in place of me.textbox.text?

What is the error that is being generated? BTW, Number is a Jet reserved word so it may not be a
good choice for a column name.

Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

This is basicly the error I get.

http://img107.imageshack.us/img107/8965/vbcn6.jpg
 
On 18 Apr 2007 05:17:50 -0700, (e-mail address removed) wrote:
Click to expand...
¤ I am using a Access Database to search/add a bunch of information in
¤ MS VB.net 2005 express. In the query (querybuilder) everything seems
¤ right, but in the WHERE statement I use [Number] = me.textbox.text .
¤
¤ Then in the statement I have an if statement
¤
¤ IF combobox.text = option1 Then
¤ e.databaseadapter.GetDataBy2() 'which is the query i built
¤ end if
¤
¤ I get an error each time, and can't figure out if it is with the sql
¤ statement, or the execution. Is there a search critria field i need
¤ to put in place of me.textbox.text?
Click to expand...
What is the error that is being generated? BTW, Number is a Jet reserved word so it may not be a
good choice for a column name.
Click to expand...
Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

This is basicly the error I get.

http://img107.imageshack.us/img107/8965/vbcn6.jpg- Hide quoted text -

- Show quoted text -
Click to expand...

Any Ideas?
 
On 19 Apr 2007 05:14:37 -0700, (e-mail address removed) wrote:

¤ On Apr 18, 1:34 pm, (e-mail address removed) wrote:
¤ > On Apr 18, 9:59 am, Paul Clement
¤ >
¤ >
¤ >
¤ >
¤ >
¤ > > On 18 Apr 2007 05:17:50 -0700, (e-mail address removed) wrote:
¤ >
¤ > > ¤ I am using a Access Database to search/add a bunch of information in
¤ > > ¤ MS VB.net 2005 express. In the query (querybuilder) everything seems
¤ > > ¤ right, but in the WHERE statement I use [Number] = me.textbox.text .
¤ > > ¤
¤ > > ¤ Then in the statement I have an if statement
¤ > > ¤
¤ > > ¤ IF combobox.text = option1 Then
¤ > > ¤ e.databaseadapter.GetDataBy2() 'which is the query i built
¤ > > ¤ end if
¤ > > ¤
¤ > > ¤ I get an error each time, and can't figure out if it is with the sql
¤ > > ¤ statement, or the execution. Is there a search critria field i need
¤ > > ¤ to put in place of me.textbox.text?
¤ >
¤ > > What is the error that is being generated? BTW, Number is a Jet reserved word so it may not be a
¤ > > good choice for a column name.
¤ >
¤ > > Paul
¤ > > ~~~~
¤ > > Microsoft MVP (Visual Basic)
¤ >
¤ > This is basicly the error I get.
¤ >
¤ > http://img107.imageshack.us/img107/8965/vbcn6.jpg- Hide quoted text -
¤ >
¤ > - Show quoted text -
¤
¤ Any Ideas?

Yes, the error indicates that one of the column names or criteria is not being recognized. I didn't
see your complete statement but it should look something like the following (assuming Number is a
numeric field):

"WHERE [Number] = " & me.textbox.text

In your example, me.textbox.text will not be evaluated until it is sent to the database engine
(which will have no clue as to what it is).


Paul
~~~~
Microsoft MVP (Visual Basic)
 
On 19 Apr 2007 05:14:37 -0700, (e-mail address removed) wrote:

¤ On Apr 18, 1:34 pm, (e-mail address removed) wrote:
¤ > On Apr 18, 9:59 am, Paul Clement
¤ >
¤ >
¤ >
¤ >

¤ > > On 18 Apr 2007 05:17:50 -0700, (e-mail address removed) wrote:
¤ >
¤ > > ¤ I am using a Access Database to search/add a bunch of information in
¤ > > ¤ MS VB.net 2005 express. In the query (querybuilder) everything seems
¤ > > ¤ right, but in the WHERE statement I use [Number] = me.textbox.text .
¤ > > ¤
¤ > > ¤ Then in the statement I have an if statement
¤ > > ¤
¤ > > ¤ IF combobox.text = option1 Then
¤ > > ¤ e.databaseadapter.GetDataBy2() 'which is the query i built
¤ > > ¤ end if
¤ > > ¤
¤ > > ¤ I get an error each time, and can't figure out if it is with the sql
¤ > > ¤ statement, or the execution. Is there a search critria fieldi need
¤ > > ¤ to put in place of me.textbox.text?
¤ >
¤ > > What is the error that is being generated? BTW, Number is a Jet reserved word so it may not be a
¤ > > good choice for a column name.
¤ >
¤ > > Paul
¤ > > ~~~~
¤ > > Microsoft MVP (Visual Basic)
¤ >
¤ > This is basicly the error I get.
¤ >
¤ >http://img107.imageshack.us/img107/8965/vbcn6.jpg-Hide quoted text -
¤ >
¤ > - Show quoted text -
¤
¤ Any Ideas?

Yes, the error indicates that one of the column names or criteria is not being recognized. I didn't
see your complete statement but it should look something like the following (assuming Number is a
numeric field):

"WHERE [Number] = " & me.textbox.text

In your example, me.textbox.text will not be evaluated until it is sent to the database engine
(which will have no clue as to what it is).

Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

SELECT [Asset Tag Number], [User], Username, [Department Name],
[Asset Name], [Asset Type], Catergory, [Serial Number], [Purchase
Order Number],
[Purchase Price], [Purchase Date], Warrenty,
Notes
FROM PPC_Inventory
WHERE '[Asset Tag Number] =' & me.ToolStripTextBox1.[text]
ORDER BY [Asset Tag Number]

is my query, in query builder. No luck still. any help?
 
On 20 Apr 2007 07:22:37 -0700, (e-mail address removed) wrote:

¤ > ¤ > > On 18 Apr 2007 05:17:50 -0700, (e-mail address removed) wrote:
¤ > ¤ >
¤ > ¤ > > ¤ I am using a Access Database to search/add a bunch of information in
¤ > ¤ > > ¤ MS VB.net 2005 express. In the query (querybuilder) everything seems
¤ > ¤ > > ¤ right, but in the WHERE statement I use [Number] = me.textbox.text .
¤ > ¤ > > ¤
¤ > ¤ > > ¤ Then in the statement I have an if statement
¤ > ¤ > > ¤
¤ > ¤ > > ¤ IF combobox.text = option1 Then
¤ > ¤ > > ¤ e.databaseadapter.GetDataBy2() 'which is the query i built
¤ > ¤ > > ¤ end if
¤ > ¤ > > ¤
¤ > ¤ > > ¤ I get an error each time, and can't figure out if it is with the sql
¤ > ¤ > > ¤ statement, or the execution. Is there a search critria field i need
¤ > ¤ > > ¤ to put in place of me.textbox.text?
¤ > ¤ >
¤ > ¤ > > What is the error that is being generated? BTW, Number is a Jet reserved word so it may not be a
¤ > ¤ > > good choice for a column name.
¤ > ¤ >
¤ > ¤ > > Paul
¤ > ¤ > > ~~~~
¤ > ¤ > > Microsoft MVP (Visual Basic)
¤ > ¤ >
¤ > ¤ > This is basicly the error I get.
¤ > ¤ >
¤ > ¤ >http://img107.imageshack.us/img107/8965/vbcn6.jpg-Hide quoted text -
¤ > ¤ >
¤ > ¤ > - Show quoted text -
¤ > ¤
¤ > ¤ Any Ideas?
¤ >
¤ > Yes, the error indicates that one of the column names or criteria is not being recognized. I didn't
¤ > see your complete statement but it should look something like the following (assuming Number is a
¤ > numeric field):
¤ >
¤ > "WHERE [Number] = " & me.textbox.text
¤ >
¤ > In your example, me.textbox.text will not be evaluated until it is sent to the database engine
¤ > (which will have no clue as to what it is).
¤ >
¤ > Paul
¤ > ~~~~
¤ > Microsoft MVP (Visual Basic)
¤
¤ SELECT [Asset Tag Number], [User], Username, [Department Name],
¤ [Asset Name], [Asset Type], Catergory, [Serial Number], [Purchase
¤ Order Number],
¤ [Purchase Price], [Purchase Date], Warrenty,
¤ Notes
¤ FROM PPC_Inventory
¤ WHERE '[Asset Tag Number] =' & me.ToolStripTextBox1.[text]
¤ ORDER BY [Asset Tag Number]
¤
¤ is my query, in query builder. No luck still. any help?

Warranty is spelled incorrectly. Is that how it appears in the database table?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
On 20 Apr 2007 07:22:37 -0700, (e-mail address removed) wrote:

¤ > ¤ > > On 18 Apr 2007 05:17:50 -0700, (e-mail address removed) wrote:
¤ > ¤ >
¤ > ¤ > > ¤ I am using a Access Database to search/add a bunch of information in
¤ > ¤ > > ¤ MS VB.net 2005 express. In the query (querybuilder) everything seems
¤ > ¤ > > ¤ right, but in the WHERE statement I use [Number] = me..textbox.text .
¤ > ¤ > > ¤
¤ > ¤ > > ¤ Then in the statement I have an if statement
¤ > ¤ > > ¤
¤ > ¤ > > ¤ IF combobox.text = option1 Then
¤ > ¤ > > ¤ e.databaseadapter.GetDataBy2() 'which is the query i built
¤ > ¤ > > ¤ end if
¤ > ¤ > > ¤
¤ > ¤ > > ¤ I get an error each time, and can't figure out if it iswith the sql
¤ > ¤ > > ¤ statement, or the execution. Is there a search critriafield i need
¤ > ¤ > > ¤ to put in place of me.textbox.text?
¤ > ¤ >
¤ > ¤ > > What is the error that is being generated? BTW, Number is aJet reserved word so it may not be a
¤ > ¤ > > good choice for a column name.
¤ > ¤ >
¤ > ¤ > > Paul
¤ > ¤ > > ~~~~
¤ > ¤ > > Microsoft MVP (Visual Basic)
¤ > ¤ >
¤ > ¤ > This is basicly the error I get.
¤ > ¤ >
¤ > ¤ >http://img107.imageshack.us/img107/8965/vbcn6.jpg-Hidequoted text -
¤ > ¤ >
¤ > ¤ > - Show quoted text -
¤ > ¤
¤ > ¤ Any Ideas?
¤ >
¤ > Yes, the error indicates that one of the column names or criteria is not being recognized. I didn't
¤ > see your complete statement but it should look something like the following (assuming Number is a
¤ > numeric field):
¤ >
¤ > "WHERE [Number] = " & me.textbox.text
¤ >
¤ > In your example, me.textbox.text will not be evaluated until it is sent to the database engine
¤ > (which will have no clue as to what it is).
¤ >
¤ > Paul
¤ > ~~~~
¤ > Microsoft MVP (Visual Basic)
¤
¤ SELECT [Asset Tag Number], [User], Username, [Department Name],
¤ [Asset Name], [Asset Type], Catergory, [Serial Number], [Purchase
¤ Order Number],
¤ [Purchase Price], [Purchase Date], Warrenty,
¤ Notes
¤ FROM PPC_Inventory
¤ WHERE '[Asset Tag Number] =' & me.ToolStripTextBox1.[text]
¤ ORDER BY [Asset Tag Number]
¤
¤ is my query, in query builder. No luck still. any help?

Warranty is spelled incorrectly. Is that how it appears in the database table?

Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

yea, thats not the problem. its spelled like that in the database too.
 
On 20 Apr 2007 08:42:18 -0700, (e-mail address removed) wrote:

¤ > ¤ > ¤ > This is basicly the error I get.
¤ > ¤ > ¤ >
¤ > ¤ > ¤ >http://img107.imageshack.us/img107/8965/vbcn6.jpg-Hidequoted text -
¤ > ¤ > ¤ >
¤ > ¤ > ¤ > - Show quoted text -
¤ > ¤ > ¤
¤ > ¤ > ¤ Any Ideas?
¤ > ¤ >
¤ > ¤ > Yes, the error indicates that one of the column names or criteria is not being recognized. I didn't
¤ > ¤ > see your complete statement but it should look something like the following (assuming Number is a
¤ > ¤ > numeric field):
¤ > ¤ >
¤ > ¤ > "WHERE [Number] = " & me.textbox.text
¤ > ¤ >
¤ > ¤ > In your example, me.textbox.text will not be evaluated until it is sent to the database engine
¤ > ¤ > (which will have no clue as to what it is).
¤ > ¤ >
¤ > ¤ > Paul
¤ > ¤ > ~~~~
¤ > ¤ > Microsoft MVP (Visual Basic)
¤ > ¤
¤ > ¤ SELECT [Asset Tag Number], [User], Username, [Department Name],
¤ > ¤ [Asset Name], [Asset Type], Catergory, [Serial Number], [Purchase
¤ > ¤ Order Number],
¤ > ¤ [Purchase Price], [Purchase Date], Warrenty,
¤ > ¤ Notes
¤ > ¤ FROM PPC_Inventory
¤ > ¤ WHERE '[Asset Tag Number] =' & me.ToolStripTextBox1.[text]
¤ > ¤ ORDER BY [Asset Tag Number]
¤ > ¤
¤ > ¤ is my query, in query builder. No luck still. any help?
¤ >
¤ > Warranty is spelled incorrectly. Is that how it appears in the database table?
¤ >
¤ > Paul
¤ > ~~~~
¤ > Microsoft MVP (Visual Basic)
¤
¤ yea, thats not the problem. its spelled like that in the database too.

You're not trying to run this from the query builder are you?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
On 20 Apr 2007 08:42:18 -0700, (e-mail address removed) wrote:

¤ > ¤ > ¤ > This is basicly the error I get.
¤ > ¤ > ¤ >
¤ > ¤ > ¤ >http://img107.imageshack.us/img107/8965/vbcn6.jpg-Hidequotedtext -
¤ > ¤ > ¤ >
¤ > ¤ > ¤ > - Show quoted text -
¤ > ¤ > ¤
¤ > ¤ > ¤ Any Ideas?
¤ > ¤ >
¤ > ¤ > Yes, the error indicates that one of the column names or criteria is not being recognized. I didn't
¤ > ¤ > see your complete statement but it should look something likethe following (assuming Number is a
¤ > ¤ > numeric field):
¤ > ¤ >
¤ > ¤ > "WHERE [Number] = " & me.textbox.text
¤ > ¤ >
¤ > ¤ > In your example, me.textbox.text will not be evaluated until it is sent to the database engine
¤ > ¤ > (which will have no clue as to what it is).
¤ > ¤ >
¤ > ¤ > Paul
¤ > ¤ > ~~~~
¤ > ¤ > Microsoft MVP (Visual Basic)
¤ > ¤
¤ > ¤ SELECT [Asset Tag Number], [User], Username, [Department Name],
¤ > ¤ [Asset Name], [Asset Type], Catergory, [Serial Number], [Purchase
¤ > ¤ Order Number],
¤ > ¤ [Purchase Price], [Purchase Date], Warrenty,
¤ > ¤ Notes
¤ > ¤ FROM PPC_Inventory
¤ > ¤ WHERE '[Asset Tag Number] =' & me.ToolStripTextBox1.[text]
¤ > ¤ ORDER BY [Asset Tag Number]
¤ > ¤
¤ > ¤ is my query, in query builder. No luck still. any help?
¤ >
¤ > Warranty is spelled incorrectly. Is that how it appears in the database table?
¤ >
¤ > Paul
¤ > ~~~~
¤ > Microsoft MVP (Visual Basic)
¤
¤ yea, thats not the problem. its spelled like that in the database too.

You're not trying to run this from the query builder are you?

Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

Yes I am, alternative?
 
On 20 Apr 2007 12:41:46 -0700, (e-mail address removed) wrote:


¤ > ¤ > ¤ > ¤ Any Ideas?
¤ > ¤ > ¤ >
¤ > ¤ > ¤ > Yes, the error indicates that one of the column names or criteria is not being recognized. I didn't
¤ > ¤ > ¤ > see your complete statement but it should look something like the following (assuming Number is a
¤ > ¤ > ¤ > numeric field):
¤ > ¤ > ¤ >
¤ > ¤ > ¤ > "WHERE [Number] = " & me.textbox.text
¤ > ¤ > ¤ >
¤ > ¤ > ¤ > In your example, me.textbox.text will not be evaluated until it is sent to the database engine
¤ > ¤ > ¤ > (which will have no clue as to what it is).
¤ > ¤ > ¤ >
¤ > ¤ > ¤ > Paul
¤ > ¤ > ¤ > ~~~~
¤ > ¤ > ¤ > Microsoft MVP (Visual Basic)
¤ > ¤ > ¤
¤ > ¤ > ¤ SELECT [Asset Tag Number], [User], Username, [Department Name],
¤ > ¤ > ¤ [Asset Name], [Asset Type], Catergory, [Serial Number], [Purchase
¤ > ¤ > ¤ Order Number],
¤ > ¤ > ¤ [Purchase Price], [Purchase Date], Warrenty,
¤ > ¤ > ¤ Notes
¤ > ¤ > ¤ FROM PPC_Inventory
¤ > ¤ > ¤ WHERE '[Asset Tag Number] =' & me.ToolStripTextBox1.[text]
¤ > ¤ > ¤ ORDER BY [Asset Tag Number]
¤ > ¤ > ¤
¤ > ¤ > ¤ is my query, in query builder. No luck still. any help?
¤ > ¤ >
¤ > ¤ > Warranty is spelled incorrectly. Is that how it appears in the database table?
¤ > ¤ >
¤ > ¤ > Paul
¤ > ¤ > ~~~~
¤ > ¤ > Microsoft MVP (Visual Basic)
¤ > ¤
¤ > ¤ yea, thats not the problem. its spelled like that in the database too.
¤ >
¤ > You're not trying to run this from the query builder are you?
¤ >
¤ > Paul
¤ > ~~~~
¤ > Microsoft MVP (Visual Basic)
¤
¤ Yes I am, alternative?

Can't you just hard code the value during testing? The query builder doesn't really know anything
about the TextBox.

If it's preventing you from saving the query then you should be able to enter the statement in the
Properties Windows for the object.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
On 20 Apr 2007 12:41:46 -0700, (e-mail address removed) wrote:

¤ > ¤ > ¤ > ¤ Any Ideas?
¤ > ¤ > ¤ >
¤ > ¤ > ¤ > Yes, the error indicates that one of the column names or criteria is not being recognized. I didn't
¤ > ¤ > ¤ > see your complete statement but it should look something like the following (assuming Number is a
¤ > ¤ > ¤ > numeric field):
¤ > ¤ > ¤ >
¤ > ¤ > ¤ > "WHERE [Number] = " & me.textbox.text
¤ > ¤ > ¤ >
¤ > ¤ > ¤ > In your example, me.textbox.text will not be evaluated until it is sent to the database engine
¤ > ¤ > ¤ > (which will have no clue as to what it is).
¤ > ¤ > ¤ >
¤ > ¤ > ¤ > Paul
¤ > ¤ > ¤ > ~~~~
¤ > ¤ > ¤ > Microsoft MVP (Visual Basic)
¤ > ¤ > ¤
¤ > ¤ > ¤ SELECT [Asset Tag Number], [User], Username, [Department Name],
¤ > ¤ > ¤ [Asset Name], [Asset Type], Catergory, [Serial Number], [Purchase
¤ > ¤ > ¤ Order Number],
¤ > ¤ > ¤ [Purchase Price], [Purchase Date], Warrenty,
¤ > ¤ > ¤ Notes
¤ > ¤ > ¤ FROM PPC_Inventory
¤ > ¤ > ¤ WHERE '[Asset Tag Number] =' & me.ToolStripTextBox1..[text]
¤ > ¤ > ¤ ORDER BY [Asset Tag Number]
¤ > ¤ > ¤
¤ > ¤ > ¤ is my query, in query builder. No luck still. any help?
¤ > ¤ >
¤ > ¤ > Warranty is spelled incorrectly. Is that how it appears in the database table?
¤ > ¤ >
¤ > ¤ > Paul
¤ > ¤ > ~~~~
¤ > ¤ > Microsoft MVP (Visual Basic)
¤ > ¤
¤ > ¤ yea, thats not the problem. its spelled like that in the database too.
¤ >
¤ > You're not trying to run this from the query builder are you?
¤ >
¤ > Paul
¤ > ~~~~
¤ > Microsoft MVP (Visual Basic)
¤
¤ Yes I am, alternative?

Can't you just hard code the value during testing? The query builder doesn't really know anything
about the TextBox.

If it's preventing you from saving the query then you should be able to enter the statement in the
Properties Windows for the object.

Paul
~~~~
Microsoft MVP (Visual Basic)
Click to expand...

Anyone use VB 2005 express and know a solution for queries using a
textbox? It hangs on the me.databaseadapter.GetDataBy2() statement,
I'm not sure what else I can use.
 
Back
Top