Access :: Cannot Get Query To Work With My Date Select Form

Forms :: Combo Box To Allow A User To Select Date And Relevant Work Details Appear In Subform

Jun 10, 2013

I have created a simple database, with 2 x tables. The first is a 'Week Ending' table, with a dateid key and Week Ending field. I have created several week-ending dates in order for a user to input details of work done the previous week. The second table contains the details of the work.

I have a one to many relationship between the dateid in the first table, and a dateid of the work details table.

I created a query based on the work details table, and created a subform from that. In a main form I linked a combo box to allow a user to select a date and the relevant work details appear in the subform. However the Week Ending field only shows one date, not a list of the dates I created.

View 7 Replies View Related

Queries :: Getting A Query On Multiple Checkbox Fields To Work With A Date Range?

Nov 7, 2014

I have developed a database which has required many checkbox fields to enable analysis. It requires to have the facility to input random/variable date ranges for statistical purposes.

I have built a query which obtains the counts of multiple fields using the following parameters in Query Builder in Access 2010. Although this comes up with the correct results for these multiple fields when I try introduce date range the results come up blank for all results.

An example of the parameters used for one of the checkbox fields in Query Builder is as follows:

Field: SumAnger: Sum([Anger]*-1)
Table: Default as only one table
Total: Expression
Show: Checked

This works fine.

My latest parameters for the date range are this:

Field: [cDate]
Table: Default as only one table
Total: WHERE Corrected! Whoops Copy & Paste Typo. Too early AM!
Show: Checked or Unchecked makes no difference
Criteria: Between [From Date:] And [To Date:]

This gives a statement in SQL view of:

SELECT Sum([Anger]*-1) AS SumAnger, Sum([Anxiety]*-1) AS SumAnxiety, Sum([Depression]*-1) AS SumDepression, Sum([Listening]*-1) AS SumListenig, Sum([Psychosis]*-1) AS SumPsychosis, Sum([Stress]*-1) AS SumStress, Sum([Other]*-1) AS SumOther, tblCommsLog.[cDate]
FROM tblCommsLog
WHERE (((tblCommsLog.[cDate]) Between [From Date:] And [To Date:]));

what I need to get this to work in Query Builder or failing that recommend some VBA script/code with embedded SQL to achieve the required report.

View 1 Replies View Related

Date Select Query

Feb 28, 2006

I have set up a query that includes:

An assignment start date
An assignment end date

and in addition a column has been set up to show me a Start Date where Assignment End Dates are Null i.e all live assignments.

When I enter a date only results for that particular date are visible.

Can anyone suggest how i can expand this to include all live assignments?

Thanks

View 1 Replies View Related

Query To Select Max Date

Jan 17, 2006

I have attached a small sample DB.

What I'm trying to do is to find the most recent payment date for each customer before 01/13/2006.

These are the results I'm looking for.
Customer 123456 should return the date 12/01/2005
Customer 12345 should returtn the date 01/01/2006

Can someone show me how to write a query to accomplish this?

Thanks

View 1 Replies View Related

Query Won't Work In A Form

Aug 1, 2007

hi

i have a DB which tracks order numbers,
the main table is a SQL table linked in, and there are lots of local tables as well, one of which is called userDetails and contains a field useremail

i want to open a form and only show records from trackermaindata that contain my useremail -

SELECT dbo_TrackerMainData.userEmail
FROM dbo_TrackerMainData, userDetails
WHERE dbo_TrackerMainData.userEmail=userDetails.useremai l;

when i double click this query in access it works and it shows me only my records, but when i try and run this query from a button (using a macro) in my form, it asks me to Enter Parameter Value userDetails.useremail

i've tried lots and lots of things so far and nothing works. i have lots of other queries which do work, but they don't reference another table - they either contain a static value or take their data from an entry box.

can anyone give any reason why this should not work? it seems straight forward enough!!

thanks for any help

View 4 Replies View Related

Queries :: Using A Form To Select Date Range

Jul 1, 2014

I'm using a form to select a date range. Using the following, and entering start date of 6/1/14 and end date of 7/1/14 I would expect to pull the records with a date of 7/1/14; however it doesn't. I have to enter and end date of 7/2/14 to pull 7/1/14 records.

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

View 14 Replies View Related

Problem With Select Query Using Date Parameters

Jan 7, 2008

Below is an example of my table "Current Status"

Manufacturer ID___SN________Current Location__Status Date______Time
A________________1____________Area 1________1/6/2008_______3:15
A________________1____________Area 2________1/7/2008_______2:10 PM
A________________1____________Area 3________1/8/2008_______1:01 PM
B________________2____________Area 3________1/2/2008_______5:00 PM
B________________2____________Area 2________1/3/2008_______3:00 PM
B________________2____________Area 4________1/4/2008_______12:47 PM

How can I design a query that will return each products latest currrent location by date, aka, the third and sixth record???? Thank you, I am relativley new to access and am struggling with this.

View 5 Replies View Related

Can't Select Date Range In Corsstab Query

Apr 23, 2008

Hi folks,
I have a crosstab query that counts the number of records that each employee has.

I need to be able to specify the date range to show though and the query won't let me use:
"Between [Type the beginning date:] And [Type the ending date:]"

I've attached a snapshot of my query design and output.
I've tried everything I could think of!!

View 2 Replies View Related

Select Query For Latest Previous Date

Oct 2, 2006

My table shows
ID ID_Clients EndDate

A particular value of ID_Clients can recur many times, but with different EndDate value each time.

I want to create a select query that displays
ID_Clients EndDate LatestPreviousEndDate

Any help with this would be much appreciated.

-Thanks

View 1 Replies View Related

Queries :: Select Query - Most Recent Date

Dec 24, 2013

How would you write a Select Query to select the most recent dates?

Select OrderDates
From Orders
Where >=Date()

No good if recent date is older than todays date!

View 3 Replies View Related

Select Query To Find Empty Date Fields

Jan 26, 2008

I am trying to create a select query on "ApprovedDate" where no approval is recorded. IsNull returns an expected type mismatch. Any ideas?
Regards:confused:

View 2 Replies View Related

Queries :: Appointment Activity - Select Query For MAX Date

Jul 10, 2014

I need to find the frequency of client's appointment activity after their last appointment in a 4 month window.

My question is:
(1) Is my SQL going to give me the last time a client was in the office within the 4 month window?

(2) What will my SQL need to like to count frequency of appointments before and after window for each client?

(3) Can I do (2) in the same SQL shown above or will I need to make a new SQL statement?

Code:
SELECT tblTest.Client, Last(tblTest.AppointmentDate) AS LastOfAppointmentDate INTO tblTest
FROM tblTest
GROUP BY tblTest.Client
HAVING (((Last(tblTest.AppointmentDate))<#4/30/2014#))
ORDER BY Last(tblTest.AppointmentDate);

Details

Date Window I need to select the clients last appointment: Jan 1, 2014 - April 30, 201

Once the last appointment has been determined for each client in that window I need to count the frequency of appointments before and after that date.

View 14 Replies View Related

Queries :: Cannot Get Crosstab Query To Work In A Form

Dec 30, 2013

When I run this query

TRANSFORM Sum([rpt LEAD TYPE SUCCESS RATE2].rec_cnt) AS SumOfrec_cnt
SELECT [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
FROM [rpt LEAD TYPE SUCCESS RATE2]
GROUP BY [rpt LEAD TYPE SUCCESS RATE2].Internal_Rep, [rpt LEAD TYPE SUCCESS RATE2].Lead, [rpt LEAD TYPE SUCCESS RATE2].Start_Date, [rpt LEAD TYPE SUCCESS RATE2].End_Date
PIVOT [rpt LEAD TYPE SUCCESS RATE2].Results;

I get valid results.

When I run the form it prompts me 3 times for each start date and end date. Then I get the following error :The Microsoft Access database engine does not recognize '' as a valid field name or expression.

I am running MS Access 2010 on a Window 7 pc.

View 10 Replies View Related

Queries :: Query To Select The Date From Two Tables On The Criteria Basis?

May 21, 2013

get the data from two tables on the basis of criteria...

I want to select the whole table1 which has 6 fields including Emp_id...

I want to select the single field from table2. field name is "Username" from second table2. will select the username on the basis of Emp_ID becaue both tables has same emp_ID.

It should be in order like. Emp id, Username, Startdate, Enddate, Hours, trainingNames.....

View 1 Replies View Related

Forms :: Query Doesn't Work To Filter In Form

Nov 4, 2013

I've got a single form ("Lead Data") that has Cascading Combo boxes that work perfectly, entering data into "tblLeadData":cboMatterTypeIDcboMatterIDcboAttyIDcboPlglID Attorney & Paralegal are the people assigned to the Matter. My problem is in finding a way to allow a specific Attorney or Paralegal to filter for only his or her records. I made a query of tblLeadData that works perfectly as a query, but when I use it as a filter in an "on click" macro event, it doesn't work. I suspect it's because of the cascading combos, because I've successfully used this kind of query based macro filter in the past.

Okay, more on how it is set up. The same people are always assigned to a specific matter, so when you pull down the Atty & Plgl combo boxes, there's only one person. So it isn't a true Parent/Child relationship, but it's working. And there were two advantages of this set up over an autopopulate set up (which I considered): 1) When I change something in the reference tables (refAtty and refPlgl), it also changes in tblLeadData & 2) in case there's an exception to the usual assignment pattern, we can just leave Atty & Plgl blank and put the correct assignment in a text box called "AssignmentNotes."

So my query of tblLeadData that works, qryLeadDataAssign, uses the following fields:

tblLeadData.AttyID
refAtty.Atty
tblLeadData.PlglID
refPlgl.Plgl
tblLeadData.AssignmentNote
Expr1: [Atty] & " " & [Plgl] & " " & [LeadAssignmentNotes]Criteria: Like "*" & [Who?] & "*"

The Join Properties in the query between tblLeadData and refAtty is set to "2: Include ALL records from 'tblLeadData' and only those records from refAtty" where the joined fields are equal." And the same for Plgl.

When I run the query, it asks me a single time, "Who?", I put in the name and it pulls up all instances of the name from any of the 3 fields. It acts as a "contains" filter, not an "equals" one.

As for my cascading combos, here are the settingsMatterTypeIDRow Source:

SELECT refMatterType.MatterTypeID, refMatterType.MatterType, refMatterType.[MatterType] FROM refMatterType ORDER BY refMatterType.[MatterType]; On Change Event:Me.cboMatter.Requery
MatterIDRow Source: SELECT tblMatter.MatterID, tblMatter.Matter FROM tblMatter WHERE (((tblMatter.MatterTypeID)=[Forms].[LeadData].[cboMattertype])) GROUP BY tblMatter.MatterID, tblMatter.Matter, tblMatter.Matter ORDER BY tblMatter.Matter;
On Change Event:Me.cboAtty.Requery

[Code] ....

I put a button on the form and put an embedded macro as an "On Click" event. The macro is an "ApplyFilter" and the filter name is qryLeadDataAssign. When I click on the button, I am asked to enter

data 3 times:Enter Parameter Value: Atty
Enter Parameter Value: Plgl
Enter Parameter Value: Who?

Clearly, the expression in the query doesn't function in the button. And the result, no matter what I put in, is that all of the records are still there, although the filtered button is activated.

I tried putting the expression from the query into the macro builder window, but I for sure don't know what I'm doing there and haven't been able to make it work.

View 4 Replies View Related

Forms :: Criteria In Query Does Not Work With Navigation Form

Oct 23, 2014

how the Forms work I am trying to put some of them in Navigation Form and my commands does not work as they did in Form."Home app" is the navigation form, "Apeluri_neinchise" is the subform of "Home app" and "Combo1" is the criteria based on which a query shows a specific data.

What is the proper formula for query criteria?

[Forms]![Home app]![Apeluri_neinchise].[Form]![Combo1] (I tried this but it's not workink)

View 3 Replies View Related

Making A Look-Up Form Work The Way I Want It To Work!

Feb 27, 2006

We are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the university�s motor pool.

To do this, I have created two tables: tblDRIVER and tblLICENSE.

The fields in tblDRIVER are:
pkfDriverIndex
strLastName
strFirstName
strInitial
strAddress1
strAddress2
strCity
strState
strZIP
datBirthDate

The fields in tblLICENSE are:
pkfUpdateIndex
fkfDriverIndex
datDateUpdated
strState
strLicNumber
datExpirationDate
ynViolations
ynActive

Information about the driver is stored in tblDRIVER and the driver�s license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.

My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).

All this sort of works- I am using a combo box (based on a query) to look up a driver�s last name (which it does) and to populate all the driver�s information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one driver�s last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.

Thanks for the help.

View 9 Replies View Related

Use Combo Box Of Date Received So User Can Select To Display Receipt Form

Jan 12, 2013

How to use combo box that drops down the date received from which the user can select todisplay "receipt form" on and after that date. [need to use macro]

View 4 Replies View Related

Function Date() Isn't Work

Jul 31, 2005

I made a program for litle hotel registration. It worked enoudh good for me but after changing a comuter function Date() isn't work
In Build-In-Functios Date() persist but every try to use it cose massage "The function you entered can't be used in this expression"
In some queries I change Date() with Now() but can't do this in all.
HELP

View 3 Replies View Related

Searching By Date Doesn't Work

May 11, 2006

I have a table called tblorders for orders. So I have a form for this, frmOrders.

I also have a form to perform various searches on the orders. So I have a date textbox on this search form and a command button which I want to open the frmOrders according to the date I enter in the textbox. But even though the date on the search form matches the date in tblorders it does open the relevant record??!!

The date textbox is linked to the date field in the tblorders but it doesn't recognise it even if the date is correct! Everything is set to Format: Short date

Any ideas? This system works for other things like serial number etc but not the date for some reason.

Thanks.

View 5 Replies View Related

Select Form Record Set By More Than One Query

Mar 15, 2006

Hi,

I don't quite know if I am heading down a blind alley here. I want to filter a form to show a subset of the records via a toggle button. Click again and you go back to the full record set.

The form is based on a query and the filter is to be based on a copy of the query with several criteria and sorts added. This will result in a record set of active projects comprising about 20% of all records (65 out of 253). The sorting sets the record order to match the main management report and so the Planner can update the active records by navigating through the record set rather than having to search for each record using the Find button.

If what I am trying to do is not the way to achieve the desired result, I'd be grateful for any pointers elsewhere.

Regards,

Keith.

View 12 Replies View Related

Select All By Default For Query From Form.

May 11, 2006

Hey Folks,

I have a form with five combo boxes on it and one button, when each combo box has an option set and I hit the button, it runs a query which looks up the values of the form items and gives me my chosen information.

However, if these fields are: FirstName, Surname, State, Town and Road, and I want to only select on 4, 3, 2 or 1 of these - it gives me nothing.

So how do i specify that if the combo box is left blank just to select all.

So by putting data only into FirstName, say Eric, it will select ALL Surnames, states, towns and roads where the firstname is Eric.

View 2 Replies View Related

Form To Select Fields For Query

Sep 14, 2006

I have written code to write a query with parameters set from selections made in a multiselect list box on a form.

I now want to add a check box for possible fields to include in the query and put these on the bottom of my form to allow users to select which fields they want to be displayed in the query result.

Can anyone advise how I can do this?

Many thanks,

Gary

View 1 Replies View Related

Editable Form With Select Query

Dec 17, 2013

I have a form with split view and data it taken from different table using join query, is there any way i can add edit form data and original table data gets changed.

View 2 Replies View Related

Calculating Number Of Work Dates From 2 Date Fields

May 29, 2005

In my Query I have a Date Received field and a Date Approved field.

I would like to calculate the number of work days, which excludes weekends, between Date Received and Data Approved fields.

It would be more desirable to calculate the number of work days, excluding weekends and governmnet obsereved holidays.. but I don't know if that can be done... If not I can stick with the number of work days excluding weekends.

How would I go about doing this? I would need to add a new column to my query?

What would I put for the field and criteria? What kind of expression do I use?

Any help will be appreciated, thanks

View 14 Replies View Related