Creating a query in vb 1

[tpowers]

I am trying to create a query with vb. the query will be pulling to dates like a between this date and this date.

Does anyone know how to do that.

thank you in advance,

Tpowers

 

[ajdesalvo]

If you are getting the dates from a form then an update query example would be:

DoCmd.SetWarnings False
DoCmd.RunSql "Update [Transactions] SET [Sold Flag] = -1 WHERE [Transaction Date] Between #" & me!FromDate & "# AND #" & Me!ToDate & "# ;"
Docmd.SetWarnings True

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[tpowers]

The way that I have the dates set up is so that they are numbers and not text fields is there any thing that needs to be changed about the above syntax, because I am getting an error that states:

Run time error '2465'
Microsoft Access can't find the field '|' referred to in your expression.

and this is what the vb editor is highlighting.

strSQL = "Select * From Cit WHERE Cit.KEY-DATE Between #'" & Me!txtFrmDate & "'# AND #'" & Me!txtToDate & "#'" And [CIT-Loc] Like " & Me!txtCit_Loc & "

Can you tell me why?

Thank you in advance,

TPowers

 

[ajdesalvo]

strSQL = "Select * From Cit WHERE Cit.KEY-DATE Between #'" & Me!txtFrmDate & "'# AND #'" & Me!txtToDate & "#'" And [CIT-Loc] Like " & Me!txtCit_Loc & "

Should be:

strSQL = "Select * From Cit WHERE Cit.KEY-DATE Between #" & Me!txtFrmDate & "# AND #" & Me!txtToDate & "# And [CIT-Loc] Like '*" & Me!txtCit_Loc & "*' "


Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[tpowers]

Ok I tried to use your code and and I keep getting a datatype mismatch. Here is what my code looks like now.

Can you please help me,

Thank you in advance,

TPowers

 

[tpowers]

Ok, great I got the query working, I was wondering if you could help me with something else.

I am tring to get the count of how many records the query returns. Now I created a query that will return the count of the records, but I can not get them to display on the screen in one of my controls.

Can you please tell me how to do that.

Thank you in advance,

TPowers

 

[RiverGuy]

Are you opening a recordset with the count query? If so, you can just say.

yourTextBox = yourCountingRecordSet![The Counting Field's Name]