SQL Help!!

[Dillinger4]

I have a table in Access with a field called Account
ID as Sunday through Saturday. Im trying to select the Account ID and the name of the field that is being passed
into the setconnection procedure but im not to sure how
to do this. strdaystored just holds the day of the week
produced from some functions. But how would i go about doing this?



Public Sub setconnection(strdaystored As String)
Dim i As Integer
Dim strSQL As String
Set db = OpenDatabase(KeySetting)
strSQL = "SELECT * FROM [Ticket Information]" = strdaystored
Set rs = db.OpenRecordset(strSQL)
Call findtickets
End Sub

private sub findtickets
For k = 1 To rs.RecordCount
MSFlexGrid1.Rows = MSFlexGrid1.Rows + 1
MSFlexGrid1.TextMatrix(k, 0) = rs![Account ID]
MSFlexGrid1.TextMatrix(k, 1) = rs![Saturday]????
rs.MoveNext
Next k
End Sub

'and then what would i reference the second rs! statement
'with since the field could be any day of the week.
Thanks All........

[spyder]

First thing I notice is that your SQL statement are a bit off


This is what i would use for the First one.

"SELECT * FROM [ticket information] WHERE accountid = " + strDayStored

On the other part, I would just use and if..
If rs![accountid] = "saturday"
textmatrix(k,1) = "Saturday"
end if

hope this helps
Spyder

[Dillinger4]

Thanks for your help Spyder but when i run that
SQL statement i get a datatype conversion
error. The strdaystored variable being passed
in has to match one of the weekday fields not
the Account ID.

strSQL = "SELECT * FROM [Ticket Information] WHERE [Account ID]= " + strdaystored

so how can i substitute all of the names of fields that i want to match strdaystored? {{{laughing }}}

strSQL = "SELECT * FROM [Ticket Information] WHERE [Saturday] or [Sunday] or [Monday]= " + strdaystored

Do you think that would work?
Thanks again Bro. =C)

[spyder]

I think that would work, But that would be a pain for every time that you need to access the Field(s)

Maybe, if you can find a way, you can use the Desc statement to grab the fields in to a string, parse the string array that is creates and then use on error resume next to "try" each one 8-) Thats a heck of a lot of work though, if it is even possible

I know that DESC works in Oracle, but I am not sure in Access

strAllFields[] = db.execute("DESC [Ticket Information]")
i=0
Do until strfields[i] = ""

temp = Left$(strAllFields,7)
on error resume next
rs = dbexecutte ("select * from [Ticket information] where [ccount id]= " + temp
i=i+1
loop

anyway you get the idea,

That is ALOT of work and might not be worth it

Wish ya Luck
-=D=-

[JHausmann]

First thing the both of you want to do is concatinate strings with the "&" operator. Using the "+" operator may work now but it's gonna give you nothing but grief.

Second,
strSQL = "SELECT * FROM [Ticket Information] WHERE [Saturday] or [Sunday] or [Monday]= " + strdaystored

won't work (as written, "[Saturday} or [Sunday]" if it passes the parser would instruct the statement to get every record). You'd have to write it like:

strSQL = "SELECT * FROM [Ticket Information] WHERE [Saturday]=" & strdaystored & " or [Sunday]=" & strdaystored & "or [Monday]= " & strdaystored

to make it do what you want to do.