SQL SELECT PROB IN VB 5

[astrogirl77]

Hi,

Im using VB 5, not 6, but am just trying to solve a simple SELECT statement
issue Im having.

I have a simple database, dbase 3 file, only has 360 records

The field and record structure looks like the following, note there are only
3 columns (fields) ;

CONST DECAN DEGREE
Taurus 8 38

All Im trying to do is lookup the first and second column through all the rows
and then return the the third value to a text box (DEGREE), so in my form
I enter a text string of "Taurus" into text1, and then the value of "8" (DECAN)
in the second text box, text2, and then I want the code to return the number "38" (DECAN) into the 3rd text box, text3.

I keep trying to get my SELECT statement to allow my use of the AND operator so I can search on both CONST and DECAN but it just keeps returning error messages... cant even get to the point of returning the DEGREE value Im after. Am posting the code below, can anyone help?

See Code ;

Private Sub Command1_Click()


Dim MyDatabase As Database
Dim MyRecordset As Recordset, MyField As Field
Dim MySQL As String, I As Integer

'Dim MySQL As Variant, I As Integer

Dim degrees_value_ref 'var that represents the number in the multiplier cell of the sq grid cell that shows prime or fib in base and or mult, this value becomes a look up value on the 9 by 9 (81) value grid
Dim degrees_value_ref2

degrees_value_ref = Text1.Text 'this line used as a test, grabs value from text box, value is simulation of sq grid mult cell value number
degrees_value_ref2 = Text2.Text 'this line used as a test, grabs value from text box, value is simulation of sq grid mult cell value number


Set MyDatabase = OpenDatabase("C:\% DEGREES", False, False, "dBASE III;")
'here in the above line, for dbase 3 files, only indicate the path where the file is to be found, but not the acutal file itself

' Create Recordset object from Accounts table.
Set MyRecordset = MyDatabase.OpenRecordset("degreez")
'here, the actual name of the dbase 3 file is indicate, 'test2', but NOT its file extention

MySQL = "SELECT degreez.* FROM degreez WHERE [CONST] = " & degrees_value_ref 'And [DECAN] = "& degrees_value_ref2"
'here the sql string finds the data


Set MyRecordset = MyDatabase.OpenRecordset(MySQL) ' Generate recordset.
'here the located record matching the criteria i wanted is targeted

var1 = MyRecordset("CONST")
'here the first part of the actual data from the record i want is stored to a variable

var2 = MyRecordset("DECAN")
'here the first part of the actual data from the record i want is stored to a variable

Debug.Print "var1;"; var1
Debug.Print "var2;"; var2


'Text2.Text = var1
Text3.Text = var2

End Sub

 

[Andrzejek]

Try:

MySQL = "SELECT degreez.* FROM degreez " _
  & " WHERE CONST = " & degrees_value_ref _
  & " And DECAN = " & degrees_value_ref2

Debug.Print MySQL

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[guitarzan]

Your sql string is not being built correctly. This may work:

MySQL = "SELECT degreez.* FROM degreez WHERE [CONST] = " & degrees_value_ref & " And [DECAN] = " & degrees_value_ref2

Or, if CONST is a Text value in the database:

MySQL = "SELECT degreez.* FROM degreez WHERE [CONST] = '" & degrees_value_ref & "' And [DECAN] = " & degrees_value_ref2

>it just keeps returning error messages.
Like?

 

[astrogirl77]

Problems still occurring but thanks to both Andrzejek & guitarzan for your
replies! I tried all your suggestions and in each case I got the same
error message ;

"Run-Time Error '3075'
Syntax Error (missing operator) in query expression '[CONST] = 'ARIES' And [DECAN] = '

 

[guitarzan]

OK, but now you need to show your new [highlight #FCE94F]MySQL =[/highlight] line, since it doesn't seem to match what Andrzejek or I posted.

 

[Andrzejek]

Since CONST is Text and DECAN and DEGREE as Numbers, your code should be:

MySQL = "SELECT degreez.* FROM degreez " _
  & " WHERE CONST = [red]'[/red]" & degrees_value_ref & [red]"'"[/red] _
  & " And DECAN = " & degrees_value_ref2

Debug.Print MySQL

Try this and show us what you get in the Immediate Window from the Debug line.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[astrogirl77]

Still getting the same error message, but here is what shows in the DEBUG window ;

SELECT degreez.* FROM degreez WHERE CONST = 'ARIES' And DECAN =

The value for DECAN is in the text2 box and should be getting picked up from there...

 

[Andrzejek]

Then try this:

MySQL = "SELECT degreez.* FROM degreez " _
  & " WHERE CONST = '" & [blue]Text1.Text[/blue] & "'" _
  & " And DECAN = " & [blue]Text2.Text [/blue]

I would use better names for my text boxes, like txtCONST and txtDECAN

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[astrogirl77]

Thanks Andrzejek, tried this and am getting the exact same error message!

 

[astrogirl77]

Hi Andrzejek, I tried this but get the exact same error message....!

 

[guitarzan]

>The value for DECAN is in the text2 box and should be getting picked up from there...

It should be, yes. If it isn't, there should be a simple reason... Add the following

Debug.Print MySQL 
[b][highlight #FCE94F]Debug.Print "Text2.Text = " & Text2.Text[/highlight][/b]

Does that come up with a blank value? Maybe the textbox control with the value for DECAN is named something besides "Text2"?

 

[astrogirl77]

thanks so much! tried that now I get ;

run time error 3464 data type mismatch in criteria expression

I have different versions of this database I can work with, one version
has first column as text file the other two as numeric

another version has all values as strings, it doesnt matter to me the data tyopes
I just want to be able to search the first two fields that match the text entries
and then return the third field value into text3 ....

what do you suggest good sir?

 

[guitarzan]

You tried what? You mean you added my [highlight #FCE94F]Debug.Print "Text2.Text = " & Text2.Text[/highlight] and got back and error 3464 data type mismatch???

How about posting your code as it is now, and explaining which line gives and error, and posting exactly what that error is?

 

[Andrzejek]

If you have different versions of your DB ("one version has first column as text file the other two as numeric" and "another version has all values as strings") - it is a nightmare to work with.

You ask for Numbers differently that for Text, or Dates. It matters big time.

If you want to deal with the 2 versions of your DB, you would have to add some checks of what type of the field you deal with and act appropriately.

It may not matter to YOU, but it does matter to your VB code.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[astrogirl77]

@guitarzan

>You tried what? You mean you added my Debug.Print "Text2.Text = " & Text2.Text and got >back and error 3464 data type mismatch???

Sorry, I should have explained in greater detail! Your advice helped me see that when I ran the extra debug statement the DECAN value wasnt even being picked up at all, it was blank! The data type mismatch return gave me the idea that the problem Im having with this is possibly related elsewhere in the code, Im declaring MySql as a string, but the value may be getting interpreted as a number. Ive been trying to hunt this down since we talked last, an error now occurs at this line ;

Set MyRecordset = MyDatabase.OpenRecordset(MySQL) ' Generate recordset.

here is the current version of the code ;

Private Sub Command1_Click()


Dim MyDatabase As Database
Dim MyRecordset As Recordset, MyField As Field
Dim MySQL As String, I As Integer

'Dim MySQL As Variant, I As Integer

Dim degrees_value_ref 'var that represents the number in the multiplier cell of the sq grid cell that shows prime or fib in base and or mult, this value becomes a look up value on the 9 by 9 (81) value grid
Dim degrees_value_ref2 'As String


'degrees_value_ref = Text1.Text 'this line used as a test, grabs value from text box, value is simulation of sq grid mult cell value number
'degrees_value_ref2 = Text2.Text 'this line used as a test, grabs value from text box, value is simulation of sq grid mult cell value number


degrees_value_ref = Text1.Text 'this line used as a test, grabs value from text box, value is simulation of sq grid mult cell value number
degrees_value_ref2 = (Text2.Text) 'this line used as a test, grabs value from text box, value is simulation of sq grid mult cell value number


Set MyDatabase = OpenDatabase("C:\% DEGREES", False, False, "dBASE III;")
'here in the above line, for dbase 3 files, only indicate the path where the file is to be found, but not the acutal file itself

' Create Recordset object from Accounts table.
Set MyRecordset = MyDatabase.OpenRecordset("degreesn")
'here, the actual name of the dbase 3 file is indicate, 'test2', but NOT its file extention


' MySQL = "SELECT degreez.* FROM degreez " & " WHERE CONST = " & degrees_value_ref & " And DECAN = " & degrees_value_ref2


'MySQL = "SELECT degreez.* FROM degreez " & " WHERE CONST = '" & Text1.Text & "'" & " And DECAN = " & Text2.Text


MySQL = "SELECT degreez.* FROM degreez " & " WHERE CONST = " & degrees_value_ref & " And DECAN = " & degrees_value_ref2

Debug.Print MySQL


Debug.Print MySQL



' MySQL = "SELECT degreez.* FROM degreez WHERE [CONST] = " & degrees_value_ref 'And [DECAN] = "& degrees_value_ref2"
'here the sql string finds the data


Set MyRecordset = MyDatabase.OpenRecordset(MySQL) ' Generate recordset.
'here the located record matching the criteria i wanted is targeted

var1 = MyRecordset("CONST")
'here the first part of the actual data from the record i want is stored to a variable

var2 = MyRecordset("DECAN")
'here the first part of the actual data from the record i want is stored to a variable

Debug.Print "var1;"; var1
Debug.Print "var2;"; var2


'Text2.Text = var1
Text3.Text = var2

End Sub

 

[astrogirl77]

@ Andrzejek (Programmer)

>If you have different versions of your DB ("one version has first column as text file >the other two as numeric" and "another version has all values as strings") - it is a >nightmare to work with.

The DB is very small - only 360 records, and I only reference it three times in code.

I have named each version of the DB differently, so I know which DBs have all string fields and which ones have a string field and two number fields.

>You ask for Numbers differently that for Text, or Dates. It matters big time.

I understand but my problem here is that Ive been trying to do the same thing different ways and none of the ways are working! Ive tried calling the DB just as string values and also as string and number, and neither approach works! What I want to do is simple, I cant understand what Im missing!

 

[Andrzejek]

It would be nice to know what you actually get in the Immediate Windwow from the Debug statement, what you actually try to execute

MySQL = "SELECT degreez.* FROM degreez " & " WHERE CONST = " & degrees_value_ref & " And DECAN = " & degrees_value_ref2
[blue]
Debug.Print MySQL
[/blue]
Set MyRecordset = MyDatabase.OpenRecordset(MySQL) ' Generate recordset.

PS I. Yes, I know what you mean with 2 different DB. What Users want we need to work on...
PS II. Please use TGML tags to show your code. it is a lot easier to read.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[astrogirl77]

Hi Andy!

When I run the sql query statement as you have it above, I get this error message ;

Run Time Error '3075' syntax error missing operator in query expression

Here is what appears in the debug window, notice that CONST returns the correct string value
but DECAN returns nothin, a blank!? Also, I have the TGML check box on already, am I forgetting anything else, do I have to always use TGML tags?

SELECT degreez.* FROM degreez WHERE CONST = ARIES And DECAN =

 

[guitarzan]

Based on the code that you posted, the only way to end up with that result (where there is nothing after the "DECAN = " in the SQL string) is for Text2.Text is empty.

Regarding TGML, you have to use tags, or use the tool buttons to create the tags. For code, surround your code inside [ignore]

[/ignore] tags. For a full list of TGML tags, click the blue question mark to the left of the Preview button.

 

[astrogirl77]

Thanks guitarzan!

I've been tweaking the SELECT statement code line, and I no longer get any error messages of any kind, however
I'm also not getting the value I wanted returned to text3 box from the DEGREE field.
I think my syntax and my punctuation was off, this seems to be fixed now but how do I get the actual value
returned from the DEGREE field? When I run this code it places the #1 into textbox3 but the correct value should
be 22!

Here is my new SELECT statement ;

MySQL = "SELECT [DEGREE] FROM degreez WHERE [CONST] = " & degrees_value_ref & " AND [DECAN] = " & degrees_value_ref2 & ";"