Passing a value on click of a calendar control to a variable 1

[WCL01]

Ok, first off what I'm attempting to do is to take my calendar control and have whatever is selected on it stored in a variable called enddate and then pass that to the query. I can't seem to get past the VB part of the code for this. I'm going to paste the code below. What should happen is when the person clicks on the calendar and sets the date the code should set the focus for both begindate and enddate. I will then run a query with those variables in them like this "where ((table1.date)>= [form]![formname]![begindate] and(table1.date)<= [form]![formname]![enddate]) or something along those lines, however whenever the calendar is clicked I get this message " Compile Error. Invalid Qualifier" and it stops on the begindate with the debugger.

Option Compare Database
Public begindate As Date
Public enddate As Date
Option Explicit

'Sets the end date to whatever is selected on the calendar1

Private Sub Calendar1_Click()
begindate.SetFocus
begindate.Text = "7/1/2002"
enddate.SetFocus
enddate.Text = Calendar1.Value
End Sub

 

[randy700]

Dates are not text fields.
Maybe this?

begindate = #7/1/2002#

Randy

 

[WCL01]

I have modified the statement a bit, actually a lot and this is now how it reads and I get no errors but now don't seem to pass the info to the sql statement.

Option Compare Database
Public begindate As Date
Public enddate As Date
Option Explicit


'Sets the end date to whatever is selected on the calendar1

Private Sub Calendar1_Click()
begindate = Calendar0.Value
enddate = Calendar1.Value
End Sub

This will give me no errors and I have checked to make sure that the Calendar0.value field is as it should be on click. However I put in my sql statement (table.field)>=[Forms]![QueryReportDate]![begindate] and table.field)<= [Forms]![QueryReportDate]![enddate])and save it and try and run it and it comes back with 0 records even though I know there are records in there. Also every time I go back to the sql statement the [] are gone and only the words inside them are left. As far as I know I need [] to define the forms I'm requesting the data from. But it also might be that the dates are not storing correctly in the variables I defined so no dates are being passed. Any idea?

 

[WCL01]

One more thing, I run the sql statement while the form that has the variables is still open so that they are not lost. I have not defined them as global variables.

 

[randy700]

How about posting your SQL?

Randy

 

[WCL01]

where((dbo_needlx.date)>=Forms!QueryDateSelection!begindate And (dbo_needlx.date)<=Forms!QueryDateSelection!enddate)

Now I did have it reading like this

Where((dbo_needlx.date)>=[Forms]![QueryDateSelection]![begindate] And (dbo_needlx.date)<=[Forms]![QueryDateSelection]![enddate])

However every time I save the [] get taken out. Keep in mind I do have a select statement above all of this along with a few other static values that the where statement must meet. If I forget about passing the values over and just put them in manually there are no issues.

 

[TheAceMan1]

How are ya WCL01 . . .

Your not using the variables you declared! You are in fact using controls on the form [blue]]![begindate][/blue] and [blue]]![enddate][/blue]. Change the exclamations [blue]![/blue] to a period [blue].[/blue] to access the variables.

BTW: the variables are public/global as long as the form is open . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

A couple comments on your naming:
1) Date is the name of a function and should not be used as a field name
2) It looks like you have a form with a name beginning with "query". I find that very confusing.
3) Your variable names shouldn't be the same as your control names.

What values are displayed in your QueryDateSelection form? How are the values from the calendar controls getting to the controls on the form?

Duane
Hook'D on Access
MS Access MVP

 

[WCL01]

Thanks Aceman that solved the issue. Its working perfect now.

 

[TheAceMan1]

WCL01 . . .

Great! ... However do give [green]credence[/green] to [blue]dhookom's post![/blue] ... It will save a great many headaches! . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[WCL01]

Yes thank you for the post dhookom. Everything you said I would normally put into practice however. The issue with the date field is not something I have control over. The query I posted pulls information from linked tables on a SQL db and I have no control over how they name the fields on that particular db. The issue with the form name I can take care of now that this program is now mine to do with as I wish. I'm not sure what you meen by the 3rd part. Are you saying that once I define "Public begindate as date" that I should then in the line of code state that something like begindate = begin and then use begin as my control?

 

[dhookom]

I think theAceman1 correctly guessed that "begindate" was not a control but a variable in the form. I generally use a naming convention that avoids this type of confusion. My text boxes (if referenced from code or expressions) generally begin with "txt". I begin date memory variable names with "dat". It's much less confusing if others need to maintain or review my applications.

Duane
Hook'D on Access
MS Access MVP