HELP!! QBF - Problem with Selecting Records in a Query by Form

[marie515]

Hi,

I created an unbound form with various fields to filter records and I want to link this form to a report or query to view the results.

I've created the form which has about 14 separate criteria to select from and I've created a query that refers back to the form (in the criteria), see sample code below:

Like [Forms]![QBF_FORM]![Analyst]

I also have a date range selection in my form and in the query, I have the following code for that field:

Between [Forms]![QBF_Form]![Beginning] And DateAdd("s",86399,[Forms]![QBF_Form]![Ending])

Problem. I can't get a report or query to return any data? Am I supposed to base the query/report on the "form" or do I base it on the "query"?

Does the above look alright? Do you see anything wrong with what I've indicated?

Any suggestions on how I can get the QBF to work?

 

[hneal98]

When running the query or report, do you leave the form open?

 

[marie515]

Hi.

Yes, the form does stay open. I added a control button to open a report (added the control button to the form footer.

Should I add a command that the form should close before running the query or report?

 

[hneal98]

You need to leave the form open.

Also, you never really explained where this code is located:

Between [Forms]![QBF_Form]![Beginning] And DateAdd("s",86399,[Forms]![QBF_Form]![Ending])

is it in a report or query, and where in the report or query is it?

 

[marie515]

Thank you so much for your help.

To answer your question, the code below is in my query (under criteria). The report only lists the fields from the query so that they would be populated with the data from the query.

Between [Forms]![QBF_Form]![Beginning] And DateAdd("s",86399,[Forms]![QBF_Form]![Ending])

Does that help?

 

[hneal98]

Well, I did a test and this is what I came up with. It seems that Access is somewhat tempremental when it comes to doing this. I added the following in my test and it worked well.

Between [Forms]![Form1]![txtStart].[value] And [Forms]![Form1]![txtEnd].[value]))


So try putting .[Value] as I did above.

 

[marie515]

Thanks.

I did what you indicated, adding the following:

Between [Forms]![Form1]![txtStart].[value] And [Forms]![Form1]![txtEnd].[value]))

I had to take out the two )) at the end..it didn't like that, but without the parentheses, it was ok.

I then went to my form, added a control button to run a query and when I ran it, I got only 4 records, even though I asked for all of the records in the form.

I wonder if the problem lies in the coding for "all". For each item on my "form", I have for the "Default Value" = "*" listed for each item. Is this ok? This means that the default is = all items right?

Any other suggestions?

Thanks again for trying to help.

 

[hneal98]

Where are you putting the "*"?

 

[marie515]

In the form, for each of the fields, e.g. CSR_Name I've set the properties for each of the fields so that the Default Value shows "*".

I saw this in someone else sample QBF.

 

[hneal98]

That will not work with a date value. The "*" is for string values. What I would do is enter default start date as #01/01/1000# and #12/31/2078# as the default end date. Also, set your field format in your form as short date. Other than that little issue, this should work fine.

 

[marie515]

Hello again

First, thank you so much for sticking with me on this. I know it is frustrating to try to figure something out without being able to look at the actual database. I did what you indicated and it's still not working. I think we're getting close though. I get 4 of the 160 records returned when I run the query now off of the form. For some reason, the query does not understand that I want all records for the period of time specified.

Question: In my QBF form, I have the two date fields -the "Date Received" and the "Date Closed" field.

In my QBF "query", however, I am using only the "Receipt Date" field to enter the criteria in.

Between [Forms]![QBF_Form]![Beginning].[value] And [Forms]![QBF_Form]![Ending].[value]

I didn't even have "close date" pulled down in my query. Should I address the "close Date" field in the query?

I think we'll have to give up on this one. I think this is a difficult one to put a diagnosis on without looking at the actual database.

Let me know if you can think of anything else. Otherwise, please accept my thanks for trying to help.

 

[hneal98]

Can you list the fields in the underlying table and some of the data and an example of what you are trying to get as results?

 

[marie515]

Sure.

Here are the fields from the underlying table that I am using in my query.

ReceiptDate
CloseDate
IssueNumber
Analyst
Subject_Name
Subscriber#
ContactName
ContactDept
CaseStatus
TypeOfIssue
County
Specialty
ResolutionCode
ResolutionMethod

-----

All of the above are in one table named "Escalation_Log". I want to use my QBF "form" so that the users can select various pieces of information to search for records that meet the criteria the user specified on a query or report for the user to view.

----
Example: Let's say that the user may want to have all closed cases for the period of February 1 - March 1, 2004, and say they want all records that show the county = Sacramento. I'd want the resulting query or report to show only those records that I've selected based on the criteria I selected.

In addition, if I do not select "any" criteria, e.g. do not specify only "sacramento" county, I want the form to default to "all records". In other words, I want the form to act as an advanced filter to pull what ever the user wants to see in a report or query.

I could send you the database (e.g. the form, query and main table (with only a few records) so you can see how I set this up?

Would that be ok?

 

[hneal98]

That would be fine. You can send it to hneal_98@yahoo.com. I will take a look at it and see what is happening. I might not get to it today, but I can look at it Saturday.

 

[jbviajero]

I'm a rank amature (so ignore what I say) but if you have any blank (Null) fields, access will not return them with wild cards (*, % et al). I've been working through this exact same problem today. Don't know if that helps at all.

 

[hneal98]

amature or not, all info is welcomed.

 

[zinja]

I have also done what you are talking about (although with lumber products for the data). What I did was to create multiple queries and then use code to determine which query to run when the user clicked the "Find" command button. I also had two date used in the data (ShipDate and OrderDate). I simply used an Option boxes to let the user choose which date to search against.

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.