input date into query button

[officemanager2]

I have created a form that can pull up a query that is tied into a specific date, but instead of changing the date parameters in the query, I would rather be able to input the date parameter on the form rather than opening the query each time. Any suggestions?

 

[PHV]

In the form create a textbox named, say, txtDate.
Then in the query use this criteria for the date field:
Forms![your form name]![txtDate]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[officemanager2]

Thanks for this, but unfortunately something still is not working as the query comes up blank? Should I have something in the control source of the textbox?

 

[lespaul]

did you enter a date in txtDate before you ran the query? what's the SQL of the query?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[officemanager2]

I believe so. I've tried a number of things since the first reply. Currently when the form is open the text box is blank, once a date is put in nothing occurs. When I run the query by itself it comes up blank with the aforementioned critera in. Remove the critera and use dates and it works again. It seems that even with the critera in place the form and the query are not linked, but I could be wrong on this.

SQL

SELECT [SEGO test].GoodTo, [SEGO test].Owner, [SEGO test].[Tenure Number], [SEGO test].[Claim Name], [SEGO test].Status, [SEGO test].Area
FROM [SEGO test]
WHERE ((([SEGO test].GoodTo)=[Forms]![Form1]![txtDate]))
ORDER BY [SEGO test].GoodTo;

I've never used view to view SQL, Interesting.

 

[lespaul]

is there a button on the form that you press to actually RUN the query after you enter the date?

 

[officemanager2]

This may have posted twice, sorry about that.

Yes there is button to run the query

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String

stDocName = "dateQ"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

 

[dhookom]

Try change you SQL to

SELECT [SEGO test].GoodTo, [SEGO test].Owner, [SEGO test].[Tenure Number], [SEGO test].[Claim Name], [SEGO test].Status, [SEGO test].Area
FROM [SEGO test]
WHERE [SEGO test].GoodTo Between [Forms]![Form1]![txtDate] - 1 AND [Forms]![Form1]![txtDate] + 1 
ORDER BY [SEGO test].GoodTo;

Make sure the form is open and there is a US formatted date entered into the text box. Then open the query.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[officemanager2]

Thanks, but still no luck.

When I put a date and click the query button on the form a message comes saying 'you canceled the previous operation'

When I leave the date field blank and click the query button the query comes up, but once again with no data in it.

I did not think this type of development would be so difficult. It still seems that the form and query are not linked, or at least not properly linked. I really don't know what to do with this.

 

[dhookom]

How about providing the current SQL view of your query? What is the date you are entering into the text box? Have you checked to make sure you have valid dates in your field?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[lespaul]

is it a DATE datatype in the table?

Leslie

 

[officemanager2]

lespaul: The field for the date is set as date/time with no format as I need to input infomation from excel sheets with a date format that does not match any of the standard formats offered by access. Perhaps I should sync this field with an input mask?

dhookhom: The way I'm entering the date is mm/dd/yyyy. As far as the SQL I've used the following two (the first one here came from yourself, and the second one is a result of the critera from lespaul) two:

SELECT [SEGO test].GoodTo, [SEGO test].Owner, [SEGO test].[Tenure Number], [SEGO test].[Claim Name], [SEGO test].Status, [SEGO test].Area
FROM [SEGO test]
WHERE [SEGO test].GoodTo Between [Forms]![Form1]![txtDate] - 1 AND [Forms]![Form1]![txtDate] + 1
ORDER BY [SEGO test].GoodTo;


SELECT [SEGO test].GoodTo, [SEGO test].Owner, [SEGO test].[Tenure Number], [SEGO test].[Claim Name], [SEGO test].Status, [SEGO test].Area
FROM [SEGO test]
WHERE ((([SEGO test].GoodTo)=[Forms]![Form1]![txtDate]))
ORDER BY [SEGO test].GoodTo;


I've tried putting a control source on the txtDate input on the form but none seem to work and all I get is #Name?

It seems close to working, but obviously something is not linked, and I would guess that on the form the run query button is not linked to the txtDate text box.

Let me know if there is anything to add. I've thought about scrapping this and staring fresh, though I don't know if that would help things either.

thanks

 

[SkipVought]

"... I need to input infomation from excel sheets with a date format that does not match any of the standard formats offered by access"

Date/Time values are NUMERIC. Has nothing at all to do with FORMAT. FORMAT (like 12/20/2007 or Thursday, Dec 10, 07) are for DISPLAY ONLY -- human consumption.

Right now in North Texas, the Date/Time value is 39436.51823, which can be FORMATTED any number of ways, including, 12/20/2007 12:26.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[PHV]

What about this ?

PARAMETERS [Forms]![Form1]![txtDate] DateTime;
SELECT [SEGO test].GoodTo, [SEGO test].Owner, [SEGO test].[Tenure Number], [SEGO test].[Claim Name], [SEGO test].Status, [SEGO test].Area
FROM [SEGO test]
WHERE CLng([SEGO test].GoodTo)=CLng([Forms]![Form1]![txtDate])
ORDER BY [SEGO test].GoodTo

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[officemanager2]

Some progess. I was doing some searching on sites and came accross a few ideas which lead to the following in the critera line

Between [Enter Start Date:] And [Enter End Date]

Ideally though I will in time get rid of the enter start date as there is no start date, only an expirey date. This seems to be working so far but as I add more variables to the query's things could get more interesting.

Thanks to everyone for their assistance, and if anyone has ideas about how I can ask only for an end date feel free to post them.

 

[PHV]

What was the criteria when you changed the date manually in the query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886