retrieving records??

[Dre313]

I have a form which stores records.. Now I need to create another form which pulls specific data in..

ie. say I need to pull in all records that were dated between.. 7/01/03 thru 7/30/03 AND where inspected by supervisor 1. Something of that nature..

I'm new to access.. so please bare with me..

thanks

 

[KenReay]

Hi

Make a query with criteria such as you describe

create a form using taht query as the recordsource

You can make the query 'dynamic' by using textboxes on a form as the criteria, the form could even be teh same form on which you display the results

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Dre313]

thanks for the response...

I do have a question.. in my query.. criteria..

i entered :

between Forms![frm_reportQualityStatus]![txtStartDate] And between Forms![frm_reportQualityStatus]![txtEndDate]

and i get an error.. invalid syntax ?

what did i do wrong ? thanks
I'm using access 97

 

[dbelsey]

Suggest you omit the second "between". Therefore should look something like this:

between Forms![frm_reportQualityStatus]![txtStartDate] And Forms![frm_reportQualityStatus]![txtEndDate]

or you could use &quot;>=&quot; and &quot;<=&quot; operators such as

>= Forms![frm_reportQualityStatus]![txtStartDate] And <= Forms![frm_reportQualityStatus]![txtEndDate]

 

[Dre313]

awesome thanks!

is there a way i can count and display how many records I have? thanks

 

[dbelsey]

Dre313, there are a couple of ways to count records:

1. In the query itself, either
a. add a field called TotalRecords calculated as follows:
DCount(&quot;[AnyFieldName]&quot;, &quot;YourTable&quot
(note the record count will be the same for each record)
b. group the records and count one of the fields
2. In the form itself create a text box called txtTotalRecords whose Control Source is
=DCount(&quot;[AnyFieldName]&quot;, &quot;YourTable&quot

If you use either of the DCount solutions, do see VB help for DCount as you will likely want to add one or more criteria.

 

[Dre313]

Ok I think I got it.. I just have a couple of question..

I'm trying to get 2 or more criterias to work together..

ie..

my first criteria was.. to find all records that were still open by dates..

my second criteria .. I was to find all records that are closed..

would i have to setup a different query just for that criteria..

In my first criteria:


code:--------------------------------------------------------------------------------
Between [Forms]![frm_reportQualityStatus]![txtStartOpen] And [Forms]![frm_reportQualityStatus]![txtStartEnd]
--------------------------------------------------------------------------------

and my second:

code:--------------------------------------------------------------------------------
Between [Forms]![frm_reportQualityStatus]![txtCloseOpen] And [Forms]![frm_reportQualityStatus]![txtCloseEnd]
--------------------------------------------------------------------------------

I put them together in the same query and i get nothing.. but if i list them seperately .. i get the fields i ask for..

thanks

 

[Dre313]

on the counting of records...

ok I have a form which adds records in with fields such as.. the date the record was opened, the date the record was closed, project #, system, area, etc...

I have a seperate form to create a report of my findings.. say that i was to search for all records from august 1 through august the 30th.. and in area B and are labeled project 1..

I want a form which the user enters in what they want out of the database.. (ie.. the date, area, project etc.. ) all criterias have to be met then it displays only those records... and in those records I want a count of how many are &quot;area B&quot;

In my table &quot;AREA&quot; would be my field name and under AREA there should be something like..

area B - 01
area B - 02
area B - 03
area B - 04
area B - 05
area C - 01
area C - 02
area C - 03
area C - 04

etc...

In the beginning the user adds records.. and chooses through a combo box these fields.. area B - 03 or area C - 03 etc.. so each record has its only area.

again,

I want to be able to have a form which searches through dates, area, project .. first.. then with those records pick out all areas of what the user asked for and make a count and display of how many those records there are..

seems like alot to ask.. thanks alot for helping..

 

[dbelsey]

Dre313, it's not really all that difficult, though it'll be a bit tedious. As I understand it you want a form on which there are several combo boxes. In the combo boxes the user chooses the area, the project,...(probably a lot more things).... You also want two text boxes: one for a start date and the other for an end date. When all criteria are chosen, you want to click on a command button which opens another form where all records meeting the criteria chosen in the first form are displayed, along with a text box which displays the number of records meeting the criteria. Is that right?

 

[Dre313]

Actually a count for just one particular field... not how many records there are... but of those records chosen in form1 .. count only those records that have...

ie..

Area B ( this is what the user selected in form 1 )

Area B is followed by numbers.. ( Area B - 01, Area B - 02, etc.. )

I want a count of all &quot;Area B's&quot;

So in the above example..Area B - 01 and 02 .. so a count of 2 would be made..

Thanks for the help.. looking forward to hear from you

 

[Dre313]

ok I think i found a way to find my question .. I was going to use something like this..

qry1
select count(<field with area in> )
from <table or query name that has the base data>
where <field with area in> like &quot;area B*&quot;;

...

I'm still having problems with my query...

I'm trying to pull certain records that meet certain criterias..

Ok i have three fields on my form...

one is a date field..
--------
here the user enters in two dates.. a starting date and an ending date.. ( this finds all records in between those dates)

2nd field is the area
--------------
Here the users enters in the area of the record..
(this find all records between those dates in field 1(date) then pulls out only the records between those dates and with that area...

3rd field.. is the code
----------
Here the user enters in the code of a record he wants to find.. ( this finds all records between those dates in field 1 and within that area and with that code)

so the finally output would be records btween the dates specified,that area, and with that code..

I have a couple of problems..

when i have more than one criteria.. in my query nothing comes up when i do my search for records.. I tested each criteria by itself.. and they work.. its just when i add more than one criteria.. no records pull up...

the second problem I'm having is..

say that the user just wants to find records in between the dates specifide.. this is the only field he enters into.. hits enter.. nothing comes up!.. . but if i take all criterias off my query except for the date.. then yes it comes up...

in other words.. I have to fill in the fields of all criteria for it to bring records in.


hope this made sense..
if you need to see my .mdb let me know.. thanks

I'm soo lost...

 

[Dre313]

Heres an update of my SQL...

SELECT tblInspections.strProject, tblInspections.strDate, tblInspections.DATECLOSE, tblQR.EPO, tblInspections.REJ, tblQR.System, tblInspections.CRT, tblInspections.INSP, tblInspections.FLT, tblInspections.WF, tblInspections.DISP, tblInspections.strArea, tblInspections.strReference, tblInspections.INSPECTOR
FROM tblInspections INNER JOIN tblQR ON (tblInspections.strReference = tblQR.strReference) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strProject = tblQR.strProject)
WHERE (((([strDate] Between [txtStartOpen] And [txtStartEnd]) Or (Not ([txtStartOpen] Is Null Or [txtStartEnd] Is Null))) And (([DATECLOSE] Between [txtCloseOpen] And [txtCloseEnd]) Or (Not ([txtCloseOpen] Is Null Or [txtCloseEnd] Is Null))) And (([REJ] Like [txtREJ]) Or (Not ([txtREJ] Is Null)))));

hmm where could i be going wrong ?

thanks for the help..