SELECT WHERE statement

JayKappy · ‎08-16-2010

I can get this query to work...but its quering a Double Field (ANGLE):

' Build Query Statement
Dim varQuery
varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ANGLE = 270"

' Set up recordset query
Dim  pRS6
Set pRS6 = pDS6.Execute(varQuery)

' Move first
pRS6.MoveFirst
        
Do While Not pRS6.EOF 
 Dim MaxID, MaxID2   
 MaxID = pRS6.Fields("OBJECTID").Value
 MaxID2 = MaxID2 & ", " & MaxID
 pRs6.MoveNext 
Loop

msgbox "Max ID: " & MaxID2

But I need to query a string field and I cant get that to work....this is what I was trying:
ID is a text field in the table

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = "01NW-001" "

I then wrote it to a variable and tried this:

Dim varUniqueID 
varUniqueID = "01NW-001"
varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = " & varUniqueID & " "

neither works....any thoughts?

THanks in advance....

PaulSteinmeyer · ‎08-16-2010

I'm not an expert in queries, but I think the literal quotation mark might be messing it up.

Instead of

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = "01NW-001" "

try

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID =  " & chr(34) & "01NW-001" & chr(34) "

-Paul

JayKappy · ‎08-17-2010

THANK YOU FOR YOUR COMMENTS....

But Yea no go....I get another error

Unterminated String Constant
Souce Text Unaviable
varQuery = "SELECT etc etc

I cant figure out how I can get the query to work for numeric fields and not text fields....
ugggg

PaulSteinmeyer · ‎08-17-2010

Aaack, I can't type (or edit). The last " is the problem, line should read

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID =  " & chr(34) & "01NW-001" & chr(34)

Again, I know little about queries but I do know that putting literal quotes in a quoted string can be a pain. This gets around that by inserting the ASCII value of the quotation mark for when you want the literal character to actually work in the string.

-Paul

JayKappy · ‎08-17-2010

Thanks again....going nuts here...went after two different fields to make sure it wasnt the field...but errors on both ....

Error:
The Column Name is not valid
Source Text Unavailable

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = " & chr(34) & "01NW-001" & chr(34)

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE DESCRIPTIO = " & chr(34) & "STOP" & chr(34)

I cant figure this out....whats going on here...

This works great for a numeric value....I agree the Literal strigns in qoutes etc can be a real pain...

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ANGLE = 270"

THanks Again...hopefully we can figure this out....

Anyone?

RuiGe · ‎08-17-2010

instead of using ", use ' for strings

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001' "

JayKappy · ‎08-17-2010

I try this:

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001' "

and when I do a message box on varQuery I get

SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001'

and nothing happens, ie the query is not returning anything...
Thoughts?

Thanks everyone for yoru help...its very appreciated...this is so simple dont know why its so tough

JayKappy · ‎08-17-2010

Whats happening with this is that the query is completly bypassing my Do While loop when I try for a string value:
varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE Suuport_ID1 = '01NW-001'"

But when I go after a numeric value it works fine:
varQuery = "SELECT * FROM SIGNS_STANDALONETABLE WHERE ANGLE = 270"

With the string query above I never get to Message Box 3

msgbox "1"
' Set up recordset query
Dim pRS6
Set pRS6 = pDS6.Execute(varQuery)
msgbox "2"

' Move first
pRS6.MoveFirst
' Loop recordset and get number of records
Do While Not pRS6.EOF
   Dim Description, ObjectID, MaxID2
   Description = pRS6.Fields("DESCRIPTIO").Value
   ObjectID = pRS6.Fields("OBJECTID").Value
msgbox "3"
   msgbox "OBJECTID: " & ObjectID

      objEFPageOneControls2("Combo1").AddItem Description, ObjectID
   pRs6.MoveNext

Loop

msgbox "4"

RuiGe · ‎08-17-2010

I try this:

varQuery = "SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001' "

and when I do a message box on varQuery I get

SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001'

and nothing happens, ie the query is not returning anything...
Thoughts?

Thanks everyone for yoru help...its very appreciated...this is so simple dont know why its so tough

It seems no record is retured based on your query definition.
I am not sure what is exactly in your data. But if you open ArcPad Studio, then open the SIGNS_STANDALONETABLE table.
Delete the "SELECT * FROM [SIGNS_STANDALONETABLE]" in the data view window,
copy
SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001'
to the window and excute the command by click on "!" on toolbar.

If the SQL sentence is wrong, error warnings will appear;
if nothing is wrong, but no record returns, you need to make sure the Field is a string field and field value is exactly what you want to query for.

JayKappy · ‎08-18-2010

It seems no record is retured based on your query definition.
I am not sure what is exactly in your data. But if you open ArcPad Studio, then open the SIGNS_STANDALONETABLE table.
Delete the "SELECT * FROM [SIGNS_STANDALONETABLE]" in the data view window,
copy
SELECT OBJECTID FROM SIGNS_STANDALONETABLE WHERE ID = '01NW-001'
to the window and excute the command by click on "!" on toolbar.

If the SQL sentence is wrong, error warnings will appear;
if nothing is wrong, but no record returns, you need to make sure the Field is a string field and field value is exactly what you want to query for.

OK here is what I did....I went back into my data and did a TRIM([field]) on a couple text fields to make sure that there were no spaces..
I then re-exported to axf/apm using the ArcPAD data manager
went into ArcStudio and opened the table.
I replaced the query and just used one of the values I saw in the window...and it worked
I then tried my original value and it would not work...
I looked a bit further and it seems that only 164 records were exported to the axf/apm file (see attachement)

Is there a record limitation on standalone tables being exported to axf/apm?

This is a related table to the main feature class and I need all the values in it....THERE was data that was not exported to the axf/apm file

How do I get all the records to export?

Thanks