Query by Name and Date

[David]

I have a table that I need to query by name and date.
I would like to ceate a button on my form so when I click it will ask for Date and Name.
I have used the query genie but would like to only select Date and Name.
Where do I need to start?

I have read the Guaranteed 1-Hour Guide to Becoming a Query Filter Expert but I'm not quite at that level (?)

[Steve Andrews]

RE: Query by Name and Date

David,

Whether you use Action Scripting or the Xbasic Script genie for the OnPush event, you can easily accomplish these prompts. First select "Dialog Boxes" as the category and "Display an Xdialog Box" as an action.

You will be prompted for all needed info. This will allow for more than one variable of different types. Give this a try and let us know if you have any difficulty.

Steve

[Stan Mathews]

RE: Query by Name and Date

The easiest way to do what you are asking is to create a saved query operation via the operations genie. I suppose you are supposed to know that your can type in

var-"askc_Name and var-"askd_Date

in the expression builder, as they are not "chooseable".

Here's the xbasic revealed behind a query operation I created to test for a solution to your request. (F01 and F39 are field names)

filter = "F01=Var-"askc_Name.and.F39=var-"askd_Date"
order = ""
options = ""
description = "Saved Query: test"
show_all_flag = .f. 'user cannot turn off the query
layout_type = "browse" 'to open a form, set this to "form"
layout_name = "" 'This will open the default layout. You can also specify a named layout here
a5_open_layout(layout_type,layout_name,"cost",filter,order,description,show_all_flag)

[Steve Andrews]

RE: Query by Name and Date

And, oh yes, ...

If cName is the name and dDate is the date, try a query order of:

dtos(dDate)+alltrim(cName)

Steve

[David]

RE: Query by Name and Date

Steve, I tried what you said, I get a prompt for a Date but nothing happens.
When I created the Dialog box, the script genie comes up and I created 2 new variables, Date as date type and Name as character. Could not find a place to put
dtos(dDate)+alltrim(cName)

I have the button on a form of 1 table and also an embedded browse. What I'm tring to do is display (in the browse) of records of a certain date and name. Then I have a calculated field showing the total for the day.

Stan, I tried what you said and even pasted your example replacing with my field names and when I run the saved operation from my form It opens another browse but no query.

When I use just the Query genie on my form, It works good.

I've tried both ways a lot and I'm sure I'm missing some thing or things on both.

I know some day the light will come on.
I do appreciate
your help.

Thanks,
David

[Stan Mathews]

RE: Query by Name and Date

"It opens another browse but no query." - The browse it opens is the result of the query.

"I have a table that I need to query by name and date.
I would like to ceate a button on my form so when I click it will ask for Date and Name." - then what? Do you want the original form to be filtered on the results of the query?

[David]

RE: Query by Name and Date

Yes, I want the filtered data on the form I have open.
When I run the operation from my form, a dialog box opens asking for Name and Date. I enter the info and a new browse opens with no query applied. All of my records are there, not filtered.

What I normally do is open my form with the embedded browse, I go to Menu bar and select Query, then Query Genie, then I select my Date field, then in the next column I select "is on the date(s)", then in the 3rd column I select the triangle in box and window opens for "Value", then I click that box and anther windows opens with all of the dates in my table, I select the Date, click OK, then if I just want the date only, i select the Run Query, then select close and there is just the filtered data for that date only.

That's what I'm trying to automate.

Thanks,
David

[David]

RE: Query by Name and Date

I got it work except I would like to have the query filter the form layout that I already have. Because I have calculated fields total data for the day.

Here is the code in the saved operation.

filter = "Date = var-"AskD_varaskc_Name_and_varaskd_Date"
order = "recno()"
options = ""
description = "Saved Query: New Date"
show_all_flag = .f. 'user cannot turn off the query
layout_type = "browse" 'to open a form, set this to "form"
layout_name = "" 'This will open the default layout. You can also specify a named layout here
a5_open_layout(layout_type,layout_name,"tm sheet",filter,order,description,show_all_flag)

Do I need to put a layout name in here? If so, where would I find the layout name?

Thanks,
David

[Stan Mathews]

RE: Query by Name and Date

To open a form instead of a browse....
filter = "Date = var->AskD_varaskc_Name_and_varaskd_Date"
order = "recno()"
options = ""
description = "Saved Query: New Date"
show_all_flag = .f. 'user cannot turn off the query
layout_type = "form" 'to open a browse, set this to "browse"
layout_name = "" 'This will open the default layout. You can also specify a named layout here
a5_open_layout(layout_type,layout_name,"tm sheet",filter,order,description,show_all_flag)

"Do I need to put a layout name in here?" - If you dont put the layout name in Alpha uses the default layout. "If so, where would I find the layout name? " - Layout name is the name you saved the form as.

You have substituted "tm sheet" in the code in the place where the table name is supposed to be. If this is a table name you would be advised to not use table names containing spaces. It is better to either close up the space, use an underscore, etc.

[David]

RE: Query by Name and Date

Hey thanks Steve and Stan.
It works great.
Thats just what I needed.

Thanks again,
David