Adodc cmdtext issue

[Spumbu1977]

Hi People

i am having a problem with my recordsource below it says invalid use of property and highlights Text18

i need to pull data out of my database table (Access 2003) and only show the CDB Numbers that reside between the two dates that get selected during runtime please can you have a look below

the field it needs to look in is called Visit Date but it can be changed

Adodc3.CommandType = adCmdText
Adodc3.RecordSource "Select * From Reports where [Visit Date] LIKE '" & Text17 & "' and > '" & Text18 & "'"
Adodc3.Refresh
DataList1.RowSource = Adodc3
DataList1.DataSource = Adodc3
DataList1.DataField = "NWC CDB"
DataList1.ListField = "NWC CDB"
Adodc3.Refresh
DataList1.Refresh

where text17 = start date and
text18 = end date

many thanks in advance
Rob

[Phil Weber]

Looks like you're missing an equals sign (should be RecordSource = "Select ..."), and I think your SQL should look like this:

"Select * From Reports where [Visit Date] LIKE '" & Text17 & "' and [Visit Date] > '" & Text18 & "'"

Finally, be aware that the above SQL will fail if Text17 or Text18 contain an apostrophe.

[Spumbu1977]

thanks Phil

do you have any idea as to why it tells me invalid use of property where as

DataList1.RowSource = Adodc3 is concerned
DataList1.DataSource = Adodc3
DataList1.DataField = "NWC CDB"
DataList1.ListField = "NWC CDB"


many thanks in advance
Rob

[Phil Weber]

Sorry, no idea.

[Spumbu1977]

HI all

Adodc3.RecordSource = "Select * From Reports where [Visit Date] LIKE '" & Text17 & "' and [Visit Date] > '" & Text18 & "' where [Report Type] Like '" & Text47 & "'"

is there any problems that you can see within this above search string

text17 = Start date
text18 = End Date
Text47 = What needs to be looked for between those two dates

it just gives me the rror as follows
Syntax error (missing operator) in query expression

any help is appreciated thanks
Rob

[Benjamin]

"Select * From Reports where [Visit Date] LIKE '" & Text17 & "' and
[Visit Date] > '" & Text18 & "' where [Report Type] Like '" & Text47 & "'"

First remark: Only use 1 where, this is not a correct SQL syntax.
Second remark: I doubt if you want to use Like, like is normally used together with wildcards, if you want to search a specified value simple use =.

This should be the outcome then:

"Select * From Reports where [Visit Date]='" & Text17 & "' and 
[Visit Date] > '" & Text18 & "' and [Report Type]='" & Text47 & "'"

If you search a text you use ' number -> nothing, date -> #. The field of the database must be a datafield ofcourse + you have to transfer your date textbox-value to date.

I'll post back to you when I've made an example

[Spumbu1977]

i am trying to create a search string but none of them seem to be working for me i have now tried to use the string as a recordset for an ADODB connection but it does not like it either

i feel like pulling my hair out lol

but i keep trying

Thanks
Benjamin