2 Parameter Values Questions

[stu_c]

Hi all
Hopefully last questions so here goes.....

1)
I have got two Parameter Values in one query on a name search so you first enter the first name then the second name.

my question is if the user wants to do a search on surnames only without having to type the first name can they type a * or something of the sort to miss the first parameter value?

2)
if for instance i have certain dates i want to look up can i get a parameter value to ask for the first date and then another parameter to ask for the end date and then automatically find the records between those dates?

Thanks

 

[Brianwarnock]

The answer is generally yes but I would use a form to drive the queryentering the parameters in formatted textboxes and firing off the query with a command button.

You can then leave a field null to select all records, ie ignore the parameter

where isnull(forms!formname!textboxname) or forms!formname!textboxname=

approach.

Brian

 

[stu_c]

any chance someone could do a quick example to shed some light?

 

[Brianwarnock]

If you were to use a parameter prompt it would have a where clauselike

Where (forename=[parameter1] or isnull([parameter1])) and surname=[parameter2]

Type it in in SQL and switch to the design grid if you prefer to see how it is entered there, ditto for the dates, experiment.

Brian

 

[KathyL]

These two are simple.


1) Use a concatination string and the "like" operand.... criteria would be

Like [Enter last name] & “*”

2) Criteria would be

Between [Enter start date] and [Enter end date]

 

[stu_c]

been trying all nighty but seem to have no luck
I have got it to ask for two dates now but when i leave the first parameter blank it shows a blank table

 

[Brianwarnock]

Had to go last noght after posting so only got your response this morning.

WHERE (([yourdate] Between [firstdate] And [lastdate]) OR (IIf(IsNull([firstdate]),([yourdate])=[lastdate])<>False)

2 points
1 beware of datetime fields containing time, as dates entered without times default to 00:00:00 time.
2 use the Parameter expression to define your parameters, easiest from the design grid
select Query from the menu bar and parameters from the dropdown, this is especially important for Date parameters so that it uses your local settings.

Brian
BTW
The <>False is not normally coded when workong in SQL, however Access puts it in if you goto the design grid, along with loads more brackets, so as I was telling you to go back to the DG for the Parameter statement I thought I'd code it to avoid any confusion.

 

[Niroth]

are you using access to design your query instead of writing your own SQL code? If you are, set the criteria under the intended field to forms!xyz!textbox1 or forms!xyz!textbox1 = null

So if you want to query for first name and last name:

1. insert two text boxes in the filter form, i'll call it "filterForm". Name one of it as FName and another as LName

Create a query as usual with the field you want to show, go into design view, and in the field containing the first name, set the criteria to: like forms!filterForm!FName & "*" or forms!filterForm!FName=null, do the same thing with the field for the last name, changing FName to LName. Nnce you have saved it, access will generate, I think like three or fours different criteria, for this scenario.

With the two dates thing. Again put in 2 textboxes into the filterForm, date1 and date2. Set the default value to the smallest date in your data for date1 and date() for date2 to include all the records if the user doesn't change anything.

Under the field containing the date you want to filter set the criteria to: between forms!filterForm!date1 and forms!filterForm!date2.


Cheers.

 

[Brianwarnock]

Niroth

I suggested a form earlier but he wants to use parameter prompts, also = null is not a check for a null field either Is Null after or IsNull before.

Brian

 

[Brianwarnock]

BTW Stu just to pre-empt your boss if he says if both dates are null give all records then
OR (IIf(IsNull([firstdate]),IIf(IsNull([lastdate]),TRUE,([yourdate])=[lastdate]))<>False)

I hope that I have got the bracketting right, air code is notorious for syntax errors.

Brian

 

[KathyL]

been trying all nighty but seem to have no luck
I have got it to ask for two dates now but when i leave the first parameter blank it shows a blank table

Are you trying this criteria???

Like [Enter last name] & “*”

 

[Brianwarnock]

Are you trying this criteria???

Like [Enter last name] & “*”

You are not answering the questions asked.

Brian

 

[Brianwarnock]

Stu I prefer that you ask the questions on the forum rather than PM me.
If the code I gave you in post 7 is not working I can only think that you have time in your date field, did you check as i mentioned this as a possible complication. If it does then use Datevalue([Date From]) ie your field.

However if you are now combining all of the criteria maybe you have got that wrong.
Unless you can reply quickly I am unlikely to be able to respond before sunday.

post your full SQL code, and let me know if there are any times involved.

Brian

 

[stu_c]

here is my SQL with the code you gave me...

SELECT [Chiltern Hire1].Forename, [Chiltern Hire1].Surname, [Chiltern Hire1].[Date From], [Chiltern Hire1].Destination
FROM [Chiltern Hire1]

WHERE (([yourdate] Between [firstdate] And [lastdate]) OR (IIf(IsNull([firstdate]),([yourdate])=[lastdate])<>False)


when i run the query i seem to be getting the following error?...

 

[Brianwarnock]

As I said in one of my posts aircode is notorious for syntax errors, just match up the () best starting in the middle and working out, then you find that there is no match for the opening ( so either remove it or add a ) at the end.

Brian

 

[stu_c]

hi,
i am still having problems witht he date!, just looked in my table the data type is Date/Time is this the problem? if so how do i do it so its only date without the time?

 

[Brianwarnock]

Covered in post # 13