Search with two unbound txt boxes and a command button. (1 Viewer)

[hardhitter06]

Hi Guys,

Access 2003.

I'm trying to set up a search on my dashboard that will locate all records within a date range. This date range will check all record's "Contract Start" date.

I would like the information to show in a datasheet so I have a qryMain and I have a frmDateRangeSearch (datasheet view) and was wondering which should I use for this to work best.

Here is the code I have behind my command button, but it isn't working:

stLinkCriteria = "[Contract_Start] >= #" & Forms!frmDash.Text2 & "# And [Contract_Start] <= #" & Forms!frmDash!Text4 & "#"

DoCmd.OpenForm "frmDateRangeSearch", acNormal, , stLinkCriteria

I ALSO tried running it with "QryMain" but I keep getting syntaax errors and am not sure what is wrong. I will attach my database and I have three Test records with Start Dates 7/1/09; 7/3/09; 7/5/09.

Thank you!!

 

[pbaldy]

Double check the name of the field.

 

[Kryst51]

What about something like this example?

 

[hardhitter06]

Thanks Paul haha...but it is still opening up in a form and not a datasheet..how do I get that to work?

Kryst, I really like your example and I would use it, BUT I need to do a File-Send To-Mail Receipiant in excel format and when I do that, it is just taking the Dates Data...is there a way to get the subform?

 

[Kryst51]

There are two forms on my database, one that uses a list and one that uses a subform. If you click on the datasheet of the subform you should be able to send to, select excel as an e-mail attachment. Or I may not be understanding your question.

I hope Paul doesn't mind me ansering your question to him, especially if I am wrong (I'm a noob at answering forum questions ), but in your form properties set it to datasheet view, and save.

 

[pbaldy]

This?

DoCmd.OpenForm "frmDateRangeSearch", acFormDS, , stLinkCriteria

 

[hardhitter06]

Kryst,

Regardless of what I click on or highlight or even in design view...the send two is just transfering over the Dates.

For the 2nd part, I do have it set to datasheet and it still is coming up as a normal single record form. I'm wondering if I have to set up the code for my command button with a query? If that's the case I would need a little help..

 

[hardhitter06]

Paul Got it! Thanks!

Kryst, thank you for your help as well, if there was a way to get that "send to" to work that would be awesome because once a user conducts this search, since it's a datasheet, the user can't see what dates they searched by. Not a big deal, but it would be something nice to have displayed.

 

[Kryst51]

Could you post the corrected version? Then I can play and see what's going on, also I think I can learn something new from your database, a way that I haven't thought to think about things.

I am happy that Your question was answered!

 

[hardhitter06]

Sure thing...

 

[Kryst51]

Are you trying to put a button on the form that will do this? Or will file/sent to/ as attachement/ select excel in the pop up box/ then send work?

 

[Kryst51]

Thanks for posting your updated database.

 

[hardhitter06]

No, I'm going through the manual steps...I dunno if you can have a button for that. The manual steps though aren't working for me.

I should add, I'm not emailing it as I'm testing this, but it does automatically attach an excel file (.xls) that I'm opening to see if it's attaching what I want.

 

[hardhitter06]

No Problem!

 

[Scooterbug]

Hardhitter,
You can send the query as an object and have it outputted in xls format. here is the relavent code:

DoCmd.SendObject acSendQuery, "qryMain", acFormatXLS, "[EMAIL="Person@mail.com"]Person@mail.com[/EMAIL]", , , "Report", "Place Message Here"

Obviously with some tweaking it should suit what you need it for. I would put the code on a command button on your switchboard. More info can be found in the help file under SendObject

 

[Kryst51]

That's Great!

 

[hardhitter06]

Thanks ScooterBug!...I will play around with it in a little.

 

[hardhitter06]

Scooter,

That is returning all of the records without the date search factored in. Do I need to add this somewhere in the line of code: "stLinkCriteria"??

 

[Scooterbug]

My bad...I'm use to using criteria right in the actual query. If you add the following WHERE clause to the end of the SQL statement for qryMain, it will filter out the unwanted records based on the text boxes on your main form:

WHERE (((tblMain.[Contract Start])>=[Forms]![frmDash]![Text2] And (tblMain.[Contract Start])<=[Forms]![frmDash]![Text4]));

With that, you will also not need to add the link criteria when opening up the datasheet form.

 

[hardhitter06]

Let me get this straight, I'm adding this new line of code to the end of my SQL code for my qryMain:

SELECT tblMain.ConsultantName, tblMain.ConsultantStreetAddress, tblMain.City, tblMain.State, tblMain.Zip, tblMain.[Contract No], tblMain.ContractName, tblMain.Email, tblMain.[Contract Start], tblMain.[Contract End], tblMain.DescriptionofServices, tblMain.[Account No], tblMain.[# of Employees], tblMain.[# of Hours Worked], tblMain.[Amount Payable Under Contract]
FROM tblMain
WHERE (((tblMain.[Contract Start])>=Forms!frmDash!Text2 And (tblMain.[Contract Start])<=Forms!frmDash!Text4));

And removing:

stLinkCriteria = "[Contract Start] >= #" & Forms!frmDash.Text2 & "# And [Contract Start] <= #" & Forms!frmDash!Text4 & "#"

From my Command buttons On Click???

If that's the case, I'm receiving an error stating "An expression you entered is the wrong data type for one of the arguments."