I have an unbound form which refere

[jimbo62]

I have an unbound form which references multiple criteria n a query. The problem is when I do not enter any of the date criteria("between" Style) and enter information in any of the other criteria the query brings back 0 records. I tried to put in an "or" statement to specify the year and all records from that year come back, no specifics. my goal is to allow the user to enter only the criteria they have or need and run the query. Is there a way to ignore the criteria except where the user enters information? Any help would be appreciated.

Thanks,

Jim

 

[Junior1544]

what is it that you are tring to do with the query?? is it the record source for a report??

when i need optional fields to be filtered like you are saying, i will usualy build the sql and use that for the recordsource...

I can help you a bit, but i have a hard time with it myself, but i know it can be done, and i can help a bit...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[jimbo62]

I will be running reports from this input form eventually.

Here is best as I can put it...
I have a table with date field(-Date Range-), part number field, reason code field, Catalog Number, Dollar ammount field.
I want to use a form to Pass Input for the criteria in a query from the single form.

Example ..If the user wants to have all records with the specified part number but chooses not to enter any of the other data he would recieve all records regardless of date with that Part Number.

Hopefully I could do this with any of the other fields entering all or only just one of the criteria.

I hope I described it well enough.

Thanks,

Jim

 

[Junior1544]

you did very well...

Can you post the SQL for the query on here, with all the parameter's in there, and i'll see if i can parse it out for you...

--James junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[jimbo62]

I Know this is huge but the Upper management wants all of the data fields returned from the query.


SELECT LD_Data.Date, LD_Data.[Catalog #], LD_Data.[Catalog Description], LD_Data.OrderNo, LD_Data.Account, LD_Data.CSO, LD_Data.WHSE, LD_Data.[Primary RGN], LD_Data.Division, LD_Data.[Trans Code], LD_Data.[Disp Code], LD_Data.[Adj Code], LD_Data.CONCAT, LD_Data.[RGA Code], LD_Data.[RGA Description], LD_Data.Vendor, LD_Data.[Vendor Name], LD_Data.[SBU Code], LD_Data.[Maj-PC], LD_Data.Quantity, LD_Data.[Sell Price], LD_Data.[Std Cost], LD_Data.[PII Cost], LD_Data.[OH Cost], LD_Data.Total
FROM LD_Data
WHERE (((LD_Data.Date) Between [Forms]![LDRCIF]![StartDate] And [forms]![LDRCIF]![EndDate]) AND ((LD_Data.[Catalog #])=[forms]![LDRCIF]![Catalog #]) AND ((LD_Data.Account)=[forms]![LDRCIF]![Account]) AND ((LD_Data.WHSE)="CDA&quot AND ((LD_Data.[RGA Code])=[forms]![LDRCIF]![RGA Code]) AND ((LD_Data.Total)>[forms]![LDRCIF]![DollarAmmont]))
ORDER BY LD_Data.Date DESC;


Whew!!

My database project is getting Huge!! this is my second revision it has 48MB of data so far for 28 facilities.

Thanks so much for any assistance..I have hit the wall with this one

 

[Junior1544]

why not in stead of selecting each field in the query builder, select the top one...

try replacing your sql with this:
Now this is just to simplify the sql for me to work with easier... this is not the solution I am working on for you...

--James


SELECT LD_Data.*
FROM LD_Data
WHERE (((LD_Data.Date) Between [Forms]![LDRCIF]![StartDate] And [forms]![LDRCIF]![EndDate]) AND ((LD_Data.[Catalog #])=[forms]![LDRCIF]![Catalog #]) AND ((LD_Data.Account)=[forms]![LDRCIF]![Account]) AND ((LD_Data.WHSE)="CDA&quot AND ((LD_Data.[RGA Code])=[forms]![LDRCIF]![RGA Code]) AND ((LD_Data.Total)>[forms]![LDRCIF]![DollarAmmont]))
ORDER BY LD_Data.Date DESC;
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[jimbo62]

Thank you I had completly forgotten about that...

Regards Jim

 

[Junior1544]

ok, now for my solution...

this may be hard to read... and it's untested (since i don't have your table or any thing...)

So try it in an offline vertion of the database... so if it's wrong and totaly erases every thing you still have every thing(standard precaution i think...)

ok, take out that code in the ondetail of the report, and put this in the on open of the report we're working with...

Please let me know if this works or not...

--James

--Code Start--

Dim mysql As String
mysql = ""


If (Forms!LDRCIF!StartDate <> &quot;&quot And (Forms!LDRCIF!EndDate <> &quot;&quot Then
mysql = mysql & &quot;((LD_Data.Date) Between #&quot; & [Forms]![LDRCIF]![StartDate] & &quot;# And #&quot; & [Forms]![LDRCIF]![EndDate] & &quot;#) &quot;
End If

If Forms!LDRCIF![Catalog #] <> &quot;&quot; Then
If mysql <> &quot;&quot; Then mysql = mysql & &quot; AND &quot;
mysql = mysql & &quot;((LD_Data.[Catalog #])= &quot; & [Forms]![LDRCIF]![Catalog #] & &quot&quot;
End If

If Forms!LDRCIF!Account <> &quot;&quot; Then
If mysql <> &quot;&quot; Then mysql = mysql & &quot; AND &quot;
mysql = mysql & &quot;((LD_Data.Account)= &quot; & [Forms]![LDRCIF]![Account] & &quot&quot;
End If

If Forms!LDRCIF![RGA Code] <> &quot;&quot; Then
If mysql <> &quot;&quot; Then mysql = mysql & &quot; AND &quot;
mysql = mysql & &quot;((LD_Data.WHSE)=&quot;&quot;CDA&quot;&quot AND ((LD_Data.[RGA Code])= &quot; & [Forms]![LDRCIF]![RGA Code] & &quot&quot;
End If

If Forms!LDRCIF!dollarammount <> &quot;&quot; Then
If mysql <> &quot;&quot; Then mysql = mysql & &quot; AND &quot;
mysql = mysql & &quot;((LD_Data.Total)>&quot; & [Forms]![LDRCIF]![DollarAmmont] & &quot)&quot;
End If


If mysql <> &quot;&quot; Then
Me.RecordSource = &quot;SELECT LD_Data.* FROM LD_Data WHERE (&quot; & mysql & &quot ORDER BY LD_Data.Date DESC;&quot;
Else
Me.RecordSource = &quot;SELECT LD_Data.* FROM LD_Data ORDER BY LD_Data.Date DESC;&quot;
End If junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[jimbo62]

Could this code be rund against a query not necessarily from a my form to a report but display in a datasheet view?

I can atach this to a command button on my form and run this against the query. I am not sure why Im am so confused except for the fact I have never really run a report from code passing the parameters from a form before.

I will test the code tonight let me know if there is any special way you intended me to run the code...?

Thanks JR
You have put forth an effort that is appreciated. And I hope to help others as well returning the favors.

Jim

 

[Junior1544]

this code needs to be put in the on open event of the form...

It must be there and no place else...

also, the form that will be calling it must be open...

be sure to take out the code you put in the on detail event... that wont work with this here...

If you'd like you can email me a copy of the front end and i can put it in there for you... up to you...

--James
(if you do want me to put it in the mdb for you, with the email include a link to this thread...) junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[Junior1544]

i made a mistake... a big one...
The code doesn't go any where in the form!~

This code goes behind the on open event of the report!

I'm so sorry.

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[jimbo62]

I have everything in place except the fields of returned data. How do I reference the fields on my report? I will try all I can.

Thanks,

Jim

 

[Junior1544]

the fields of returned data?? I thought you had to report built already, just tring to control the filtering...

Ok, go into the report in design view... set the record source... build the report... then set the record source to &quot;&quot; and save and exit the report...

then try to run it from the built form...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.