Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Date as variable in recordset query????

Status
Not open for further replies.

bluenoser337

Programmer
Jan 31, 2002
343
CA
I need to query via ADO based on a single date coming from a calendar value control. This works... [Totals.Open "Select sum(a), count(*) from myTable where proddate like '10/2/2002%'", CN] BUT I need the date to come from a variable such as monthview.value from the calendar. How can I replace the '10/2/2002%' with this variable? VB6, SQLServer7. Thanks!
 
Try this:

[Totals.Open "Select sum(a), count(*) from myTable where proddate like '" & format(dtpicker1,"d/m/yyyy") & "%'", CN] Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'
 
johnwm...Tried the code (had to modify it as to the location of my dtpicker) but get the error "Runtime error 3709 - Operation not allowed on an object referencing a closed or invalid connection". Here is my line of code copied and pasted [Totals.Open "Select sum(a), count(*) from myTable where proddate like '" & Format(frmOverview.DTPicker1.Value, "m/d/yyyy") & "%" ', CN]. If I substitute the ['" & Format(frmOverview.DTPicker1.Value, "m/d/yyyy") & "%" '] with ['10/3/2002%'] (note:month is first), it works...but I need that variable in there. note: In debug, I am getting 10/3/2002 from frmOverview.dtpicker1.value in this line. Any ideas? Thanks so far!
 
1. Is frmOverview still open and accessible?
2. If dtpicker format is already OK, then you can dispense with the Format(......,"....") and just leave dtpicker1.value Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'
 
johnwm...yes, frmOverview is always open as a child form to the main form where the code in question is located. As I mentioned, frmOverview.dtpicker1.value is valid when running but the line of code gives an error. I've tried removing the format and changing everything else I can think of with no luck. ?? ?? ?? ?? ??
 
It looks as if something has upset your connection.

Put a break in on the offending line and check CN.State

You should get 0 for closed, 1 for open and 4 for Executing Let me know if this helps
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'
 
Thanks for your help...got it working with...
[proddate like '" & frmOverview.dtpicker1.value & "%" & "'"]
(think I was missing some of the stuff after the "%")
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top