Noice SQL Question 1

[Trudye]

Hi Everyone:

I have a VERY novice question. I know that SELECT * will bring all fields and all occurances of a certain criteria. What I don't know is how to request certain fields and still get all of the records that meet a certain criteria:
My code only get me the first occurance:

If get1strec = "False" Then
gaKey = cboHH.ItemData(cboHH.ListIndex)
sSQL = "SELECT Fnamec, Lnamec, related, sex, dobc"
sSQL = sSQL & " FROM HHold"
sSQL = sSQL & " WHERE scccc_ID = "
sSQL = sSQL & sSID
GridRec.Open sSQL, adoCNN
End If

How can I alter the above code to get all of the recs
Where scccc_ID = sSID? I know that certain records have multi occurances but I cannot get them to show.

Thanx
Trudye

 

[Chad123]

First of all, unless sSID is a number, then you will need to surround the value with single quotes(')...

Also, the query that you have there WILL return all instances that meet those requirements.

If you only want the One instance of it, then use DISTINCT...

SELECT DISTINCT ....

 

[Trudye]

Feast or famine. Now I'm getting all of the records in the table. I changed the code to following:

If get1strec = "False" Then 'User entered via GA form, this code allows the user to stay with the same family
gaKey = cboHH.ItemData(cboHH.ListIndex)
sSQL = "SELECT Fnamec, Lnamec, related, sex, dobc"
sSQL = sSQL & " FROM HHold"
sSQL = sSQL & " WHERE 'scccc_ID = & sSID'"

GridRec.Open sSQL, adoCNN
End If

Thanks
Trudye

 

[AkutaSame]

Trudye,

What Chad was just telling you is that your string should look like this (or similar):

If get1strec = "False" Then
gaKey = cboHH.ItemData(cboHH.ListIndex)
sSQL = "SELECT Fnamec, Lnamec, related, sex, dobc"
sSQL = sSQL & " FROM HHold"
sSQL = sSQL & " WHERE scccc_ID = '"
sSQL = sSQL & sSID
sSQL = sSQL & "'"
GridRec.Open sSQL, adoCNN
End If


I think I'm the only person in the world who likes to use one long line for my SQL queries... just looks better to me for some reason, but... to each their own

 

[Chad123]

no no, what I meant was this:

If get1strec = "False" Then
gaKey = cboHH.ItemData(cboHH.ListIndex)
sSQL = "SELECT Fnamec, Lnamec, related, sex, dobc"
sSQL = sSQL & " FROM HHold"
sSQL = sSQL & " WHERE scccc_ID = "
sSQL = sSQL & "'" & sSID & "'"
GridRec.Open sSQL, adoCNN
End If

 

[Trudye]

Now I'm getting a type mismatch. Both scccc_id and sSid are numeric fields. Which I assume means they should both be encased in single quotes. Should it look like this?

If get1strec = "False" Then
gaKey = cboHH.ItemData(cboHH.ListIndex)
sSQL = "SELECT Fnamec, Lnamec, related, sex, dobc"
sSQL = sSQL & " FROM HHold"
sSQL = sSQL & " WHERE 'scccc_ID = "
sSQL = sSQL & sSID
sSQL = sSQL & "'"
GridRec.Open sSQL, adoCNN
End If

Trudye

 

[Chad123]

Ok, so the Value you wanna base your criteria on is a number, and the field datatype is a number?

If so, then you do not need single quotes at all. However, if the value is a string, then you will need to encase the criteria like such: WHERE x = 'value'

So yours (granted sSID is a string and not a number) should look like this:

If get1strec = "False" Then
gaKey = cboHH.ItemData(cboHH.ListIndex)
sSQL = "SELECT Fnamec, Lnamec, related, sex, dobc"
sSQL = sSQL & " FROM HHold"
sSQL = sSQL & " WHERE scccc_ID = "
sSQL = sSQL & "'" & sSID & "'"
GridRec.Open sSQL, adoCNN
End If


If sSID's datatype is a number, then you do not need to include the single quotes.

haha, do you get what I am saying, or did I lose you?

 

[Trudye]

No its clear as mud (LOL).

If both values are numbers then no single quotes are required. If the criteria value is a string and the table value is a nubmer then single quotes are required around the criteria value.

But what if the table value is numeric and the criteria value is string? Then single quotes must be placed around the table value? Not an issue just asking for FYI.

But Chad this brings me back to me original problem. Why am I only getting one record when the table has two records with the same key?

Thank you so much for your patience
Trudye

 

[Chad123]

haha, you had some of it right.... You can only place values that match that fields datatype... if the field datatype is set to Number, then you can only place a numeric value in that field.

In your query, you will never need single quotes aroung the field you are wanting, only the values you are wanting, like such:

WHERE <field> = '<value>' (where <value> is a string datatype)

However, in your case, where the field datatype is a number (what I am gathering anyways...) then your expression should be:

WHERE <field> = <value> (where <value> is a number datatype)

Now, if you have two instances of sSID in your table in the scccc_ID field (numeric datatype), then it should return both instances.

 

[AkutaSame]

Chad,
The code you provided right after my code produces the exact result as to what I was saying you were implying.

Trudye,
The numbers that you are trying to use, indeed, must be encased with single quotes. It is a requirement of SQL.

In a single line, I will reproduce your query, and then how it should look as a correct query.

Previous, incorrect query:
sSQL = &quot;SELECT Fnamec, Lnamec, related, sex, dobc FROM HHold WHERE scccc_ID = &quot; & sSID
(Missing single quotes around the sSID)

Correct query:
sSQL = &quot;SELECT Fnamec, Lnamec, related, sex, dobc FROM HHold WHERE scccc_ID = '&quot; & sSID & &quot;'&quot;

Say the sSID was 24365, the query would look like this:

sSQL = &quot;SELECT Fnamec, Lnamec, related, sex, dobc FROM HHold WHERE scccc_ID = '24365'&quot;

 

[Trudye]

You were correct O' Wise One. It now works.


Thank you so much Chad
Be well,
Trudye

 

[Chad123]

You are correct, except for one thing.... You do not place single quotes around values where the field has a numeric datatype... thusly, if sSID is 24365 then the query would look like such:

sSQL = &quot;SELECT Fnamec, Lnamec, related, sex, dobc FROM HHold WHERE scccc_ID = 24365&quot;

if the datatype of the field is string for example, then the query would look like such

sSQL = &quot;SELECT Fnamec, Lnamec, related, sex, dobc FROM HHold WHERE scccc_ID = '24365'&quot;

 

[Trudye]

Got It.

Thanks to both of you for your explainations and for hanging in there with me.

Be well,
Trudye

 

[Chad123]

Hey,

No problem Trudye. Glad to have aided in the functionality of your app... If you make it big, make sure to add me as a reference...lol

Take care
Chad