Date Expressions For Use In Query

Feb 6, 2006

I'm trying to create a query which will retrieve holidays for a specific week, so it would allows me to enter WeekDesc (e.g. week 1) and Line (e.g. Line 1) and then retrieve some information relating to holidays. I have gotten this far already.

However, I've just realised however that for example, if I enter Week 1 and Line 1, it will only retrieve those records for which (in the Holidays Table) have Week Description as Week 1 (this other fields in the table are HolidayID (pk), PersonID (pk), StartDate (pk), EndDate and Approved (checkbox). However if the holiday runs for over a week and runs into Week 2, if i run the query for Week 2 and Line 1, it will not retrieve that holiday.

View Replies

Date And Time Expressions

Feb 27, 2005

hi all, Im working on a simple timesheet to keep track of time worked, but ran into two problems.

1.) The expression i'm using works fine for some times, and others not so good (get negative number).

2.) It only figures the hours but not the mins. (it rounds them up).

Does anyone one have a sampale DB or know a expression dealing with figuring out time differences.
If you need more explaintion on what i'm trying to do I've attached a sample DB.

Thanks,
Rich M.

View 1 Replies View Related

Queries :: Expressions - Earliest Date Of When Documents Received

Jun 12, 2014

I have the below listed expression, which will give me the earliest date of when documents were received.

However, if I only have a date in the "B - Datasheets" field, it will not appear on my report because it starts off "isnull".

How can I end the expression to use this date field if there are no other dates?

Submittal: IIf(Not IsNull([BID_PO]![B - Datasheets]) And [BID_PO]![B - Datasheets]<[BID_PO]![C - Curves (Performance Data)] And [BID_PO]![B - Datasheets]<[BID_PO]![D - Schematics] And [BID_PO]![B - Datasheets]<[BID_PO]![E - Drawings],[BID_PO]![B - Datasheets],IIf(Not IsNull([BID_PO]![C - Curves (Performance Data)]) And [BID_PO]![C - Curves (Performance Data)]<[BID_PO]![D - Schematics] And [BID_PO]![C - Curves (Performance Data)]<[BID_PO]![E - Drawings],[BID_PO]![C - Curves (Performance Data)],IIf(Not IsNull([BID_PO]![D - Schematics]) And [BID_PO]![D - Schematics]<[BID_PO]![E - Drawings],[BID_PO]![D - Schematics])))

View 2 Replies View Related

Expressions In A Query

Feb 29, 2008

Hi all!

I'm new to this forum and could use some help on a simple Access app. I have attached the relations for the db. I need to collect some data to a subform as:
tblFunctionCodes.FunctionCode,
tblAccounts.AccountNumber,
Monday (hours),
Tuesday (hours),
Wednesday (Hours),
Thursday (hours),
Friday (hours),
Saturday (hours),
Sunday (hours)
Where I have the tblEmployees.EmployeeID, tblDates.DateWeek and tblDates.DateYear as variables.
The weekdays need to be an expression with my current design, which seems to work ok from Monday to Saturday, but when I throw in Sunday Access says the query is too complicated.

I have this query to get the data, but with no expressions (can't get it to work):

SELECT tblFunctionCodes.FunctionCode, tblAccounts.AccountNumber, tblRegistrations.Hours, tblDates.DateWeekDay
FROM tblFunctionCodes INNER JOIN (tblEmployees INNER JOIN (tblDates INNER JOIN (tblAccounts INNER JOIN tblRegistrations ON tblAccounts.AccountID=tblRegistrations.AccountID) ON tblDates.DateID=tblRegistrations.Date) ON tblEmployees.EmployeeID=tblRegistrations.EmployeeI D) ON tblFunctionCodes.FunctionCodeID=tblRegistrations.F unctionCodeID
WHERE (((tblRegistrations.EmployeeID)=[intEmployeeID]) AND ((tblDates.DateWeek)=[intWeek]) AND ((tblDates.DateYear)=[intYear]));

I've been trying to use that as a foundation for other queries but I just can't get it to work. Does any of your guys have any ideas? Maybe an INTERSECT query??

Anyway I'm out of ideas and very pressed to makes this work. Any ideas are welcome!

Thanks in advance, jon.

View 10 Replies View Related

Query Calculation Expressions

Nov 28, 2005

Is there a way or what is the expression used if I want to do a running total in a query under a field called "Amount Donated". In addition, I would like to filter by selection using the DonorID as the primary key to show the total.

Thanks.

View 1 Replies View Related

Using Expressions In An Update Query

Mar 22, 2007

I want to be able to update stock levels by clicking a button on a form. The button executes code to run the updatequery. Details of Items are listed on a sales form. The fields include item code and quantity sold. Details of the items are stored in a table called 'Items' - fields include item code and quantity in stock. I want the clicking of the button to compare the item code in the form with the item codes in the Items table, and then reduce the quantity in stock by the quantity sold in the form.

Any help given would be much appreciated.

thanks

View 1 Replies View Related

Complex Expressions Or VBA Functions As Criteria In A Query

Aug 4, 2005

Hi there,

I have a list (listA) which has a record source of a query object (queryA).
The form (formA) which has listA in it, also has a text box for some of the fields requested in queryA. The goal is to provide a flexible filter for the records in the list, so the user can just type in 'K' in text box filter_name, and they will only see records matching the entry.

N0w, the scene is set! I can easily go into the query object, and in criteria type something like this:

Like "*" & [Forms]![formA]![filter_name] & "*"

This works! It flexible filters out the records matching the text box value..
But, if the record has NULL for the name, then it won't even match "**", which is the minimum criteria. So therefore I get exclusion of records, which I dont want.

So I thought perhaps there might be some control structure I can use in expressions... some if, else, end if statement? Does anyone know how to do this?

Alternatively I thought, I could write a function like this..

Function criteria(ByVal val As Variant) As Variant
If (IsNull(val) Or IsEmpty(val) Or val = "") Then
'do nothing
Else
criteria = "Like *" & val & "*"
End If
End Function

And then in the queryA object for criteria you just use:

=criteria(Forms!formA!filter_name)

But this doesn't seem to work.. and I dont know how to debug this...

Any ideas? I thought the function was a neat solution, but it seems not!

Thanks in advance!

Hiero

View 2 Replies View Related

Domain Aggregate Functions In Query Expressions

Apr 3, 2007

Alright, I've got all the pieces to this puzzle, I just can't fit them together properly. I have two tables, tblTickets and tblTicketNotes. They are in a one-to-many relationship, there can be multiple Notes attached to a single ticket. I am trying to set up a query that will pull the first (earliest by date) note for each individual Ticket. Here are the fields from each table that would be of concern here:

tbTickets
TicketID - Autonumber - Primary Key
Issue - Text - I will be using this later as a criteria to limit with, but not neccessary

tblTicketNotes
NoteID - Autonumber - Primary Key
TicketID - Long Integer - This is the foreign key of the relationship
DateStamp - Date/Time - This is the Note Date, I only want the first (earliest date) one
Content - Memo -This is the note information I want

So all I want is the Content of the first/earliest Note for each individual Ticket. I know this should be fairly easy, but I am at a loss.

View 2 Replies View Related

Queries :: Run Multiple Expressions On The Same Data Field In A Single Query?

May 11, 2014

I am trying, and failing quite spectacularly to create a single query that will contain multiple expressions on the same field.

Please see attached jpg for example.

SQL: SELECT Count(StockData.status) AS FaultyPCs
FROM StockData
HAVING (([StockData].[Status] In ("Waiting Repair") And [StockData].[DeviceType] In ("Thick PC")));

What I want to be able to do is then add additional expressions to filter and count in the same way for "Thin PC" "Thin Laptops" "Thick Laptops" and so on.

But when I add one of these expressions to the next column in the builder, it seems to apply itself to the expression to it's left, causing that one to now give a result of 0.

Is there a way of separating one from another or do I have to create a separate query for each expression and then use another query to pull them all together (as I have done in another situations - but this will involve creating probably 20 separate queries.

View 7 Replies View Related

Using Expressions And Current ID Of A Form To Create A Query And Print A Report

Mar 6, 2013

I want to have Access run a query, open a report, and print the report based on the ID number of the form that I have open. Is this possible?

The situation would be, I have a form that uses the the primary key as a record number. After I enter the data into the form, with the form open I want to add a button to open and print a report of the current record number.

View 3 Replies View Related

Expressions

Mar 4, 2008

I have a table that has questions that require Yes/No answers, simple done, however now for the tricky (or at least for me, VV new to Access) I need one of the fields to add up all the Yes and asign a value so for example:
Q1 Q2 Q3 Q4 Q5 Result
Y N Y Y N 3
How do I do this, I asume it is down to the expression for 'Field' Result but what should that expression be and where do I put it?

View 7 Replies View Related

Expressions

Jul 29, 2006

Help me what is wrong with this expression:eek: :

=IIF( [eBay Method] ="Auction",
IIF( [Start up Price] <=.99,.2,
IIF( [Start up Price] < = 9.99,.35,
IIF( [Start up Price] < =24.99,.6,
IIF( [Start up Price] < =49.99,1.2,
IIF( [Start up Price] < =199.99,2.4,
IIF( [Start up Price] < =499.99,3.6,
IIF( [Start up Price] >499.99,4.8," "
))))))),
IIF( [eBay Method] ="Buy it Now",
IIF( [Start up Price] <=9.99,.05,
IIF( [Start up Price] <=24.99,.1,
IIF( [Start up Price] <=49.99,.2,
IIF( [Start up Price] <=49.99,.25," "
))))))

View 4 Replies View Related

Unsafe Expressions

Jul 13, 2005

I'm using Windows 2K SP4 and have just installed Office 2003. Everytime I open Access I get a message asking if I want to block unsafe expressions (presumably macros). How can I turn this off?

BTW I've downloaded all of the security updates, which are recommended by MS.

View 7 Replies View Related

Unsafe Expressions...........??

Apr 5, 2007

Hi,

Can anyone tell me why it is that when i have written a DB on one machine using Access 2003 and then subsequently put that DB on to another machine i get an error relating to unsafe expressions?

why does this happen and is there a away in which can stop this error message from appearing?

I am trying to implement the DB i have written at work but it does not look good when i get a constant error message appearing when ever i open the DB.

Thank you in advace for your help

Regards
Bev

View 3 Replies View Related

Question Regarding Expressions

Feb 29, 2008

1)I have a list of stock opening prices and closing prices for March. i need an expression or function that will calculate which company's stock price changed most during the month (difference between opening and closing price). 2)In the stock table, I have dates given to me in the following format mm/dd/yyyy. Lets say I want it only to display the date so it only shows the month but not the year of the day. What function will give me this result?Thanks

View 1 Replies View Related

Questions About Possible Expressions

Jan 8, 2007

I have a complicated question here. I am currently building a db for career development in my workplace. The main fields that i am having some issues with is the following. I have three fields labelled as the following:

1)Last Review date

2)Next review date ( i have this entered in as an auto generated date-- will add one year from the last review date)

3)Status

Now here is my question:

I want my status field to auto generate as well-- say if the last review date was in 2005 and the next review date was dec.12/06-- then the status would currently be overdue.
Is there any expression to use to auto generate the status field to say whether it is overdue or completed? or is there any easier way to do this?

View 5 Replies View Related

Counts And Expressions

Jul 8, 2005

I�m starting to wonder how I ever made anything work, before I found this place.

I built 2 queries and put them in a query

They share a common field

The join type is all record from Query1 and Matching from Query2

Next I added a count field for each

Then I added a calculated field that subtracts the count from Query1 to Query2.

So far it all works

When I add >0 to the criteria for the calculated field a dialog box pops up asking for the values of the fields expressed the calculated field.

View 2 Replies View Related

Text Expressions

Jul 19, 2006

i have a master table with various lookup feilds. i want to create a querie that creates a text expression feild called description and it is based on the info provided in the lookup feild. problem is when i create this querie from the location master it provides the id's from the lookup colums and not their text.

any solution

View 4 Replies View Related

Help Need For Expressions Builder

Aug 1, 2006

Hi experts

I have some problems consists of using Conditions.
I have 4 tables in Access:
tbl1_School names, addresses, ID_Number etc..
tbl2_ School features with Number of Clesses, Number of Students, total Area etc.
tbl3_teaching stuff with names, occcupations, Ages ets. and
tbl4_nonteaching personal with names, occupations. ages, etc.

References is ID_Number of schools
I make Query with relations, but do know how to build Expressions Builder for this:
Question 1.
a)If school have between 300&500 Students there are ig.18 teachers,
b)if schools have between 500&800 Students there are ig.25 teachers and until 1500 Students there are ig. 50 teachers.
Question 2.
c)if school have area until 400 m^2 there are needed ig. 8 nonteaching personel,
d)if school have from 400 till 600 m^2 there are needed ig. 15 nonteaching personel.

Numbers I specified like examples ig. were prescribed bye regulation.

If someone want to help me how can I use Expression Builder or VBA Code it would be appreciated.

It means I should use Condition Commands.

Thank You for help.

View 6 Replies View Related

Using Expressions As Criteria

Jul 17, 2007

Is there anyway to use an if statemet to define the criteria for a query?

as in

if forms!RunReport!cboname <> "" then
query all names that equal forms!RunReport!cboname
end if

also is there any way to use a dlookup function (or another similar function)
to define several criteria for my query

ex.
i have a table, "modules", with a list of modules, each classified as "digital" or "analog" and i want to view all records containing analog modules from a different table, "info"

thanks for any help in advance

View 4 Replies View Related

Help With Expressions On Forms

Jul 6, 2005

Hi all, I'm writing a database basically as a favour to someone. I'm a sql analyst/programmer so my access and vba skills are a little limited and I hope someone can help me with a problem I have.

Part of one of my forms is a flow chart style set of Yes/No questions and the user answers each question. For each question I have created a (hidden) text box which calculates a score (5 for no, 10 for yes) which I have done with a IIF expression in the Control Source of the text box. These are then totalled to give an overall score.

This score is then used to calculate a grade (A or B) and a percentage score using the same IIF method in the control source.

What I want to do though, is use this grade and percentage and write it to the individual record as data in the table - to use in reports and such like.
Is there any way I can do this? I've tried a few bits of VBA but to no avail. Is there a way that this can be done? Or have I approached this in entirely the wrong way??!!? :confused:

Any help would be greatly appreciated!

View 2 Replies View Related

Expressions In Forms

Nov 24, 2005

Hi,

Is there a way on Access that when a total is calculated using an expression on a form that it could update the table itself with that total?

Thanks for any help!

Roly

View 1 Replies View Related

Queries :: Date / Time Query - Return All Records Of Specified Date Or Date Range

Aug 19, 2015

I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.

I have tried

Code:
Between [StartDate:] And [EndDate:]

And

Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"

Neither of which work ....

View 13 Replies View Related

Using Switch Functions In Expressions.

Oct 10, 2005

i'm trying to use a switch function in a query but i'm unfamiliar with the format a bit. here is what i'm trying to do...

switch([Radios]![Warranty] and [Radios]![HousingWarranty]=true,[Radios]![Change front housing]*(25/60*28), [[Radios]![Warranty] And [Radios]![PortableHousingWarranty]=true], [Radios]![MaterialsUsedPrice1])

basically first statement is condition second is execution. I need to add more but i'm getting an error from this much...if I was doing this in vb.net I would just do if, ifelse, ifelse, ifelse...but...

Thanks,

View 2 Replies View Related

Unsafe Expressions A2K2

Jan 9, 2007

I have recently deployed an application at work [work pcs all running A2K2], built with A2K3 on home computer.

I split the database housing the backend on our server, named server, and using frontends on users pcs.

I am running a VB script that takes information from another linked db on server (so everything would be housed together and not on users pc) this script runs a query converts to xml, opens FTP and uploads the query along with .pdfs to the internet.

I can get it to work from home, no problem. I can get it to work from my pc at work.

But when I try on other pcs it can not find the db (error msg) or opened by another user in exclusive mode, yes I explored this option I dont think this it. 1) Cause the frontend can see the backend and everything works from there. 2) That pc is the only one with an open frontend. 3) When I try opening the actual db from the networked shared folder I get an unsafe expressions alert and can not open. The VB needs to open pull from the 2nd db. Both the backend and 2nd db I get this message.

Can anyone tell me how I can change this, the msg box says its out of trusted network. So I copied and actually opened on local pc, I could open it. Move it back to server and no such luck.

Thanks for any posts, I am having to get this working before I load for other branch offices.

I have googled and searched forum, seems everything is geared towards "sandbox".

View 4 Replies View Related

Looking Up Calculation Expressions In Table

Feb 22, 2006

First off, I am not sure if this belongs in the queries forum but it seems most of the question is geared towards a table so I have posted it here.

At the moment I have a query that contains several fields that use nested IIf statements to determine which set of rules to apply. I was wondering if it is possible to somehow set the IIf variables up in a table and then for just to lookup the table in the query.

For example, one of my simpler IIf's looks like this...

Cost:
IIf([AType]='Skill',
IIf([StartDate]<#01/08/2004#,
IIf([AOP]>0,
([BasicRate]-[Frame])/[AOP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion])

So I was thinking could I setup a table to show:

FieldName AType StartDate AOP Calculation
Cost Skill <#01/08/2004# >0 ([BasicRate]-[Frame])/[AOP]/100*[Proportion])
Cost <>Skill Is Not Null Null ([BasicRate]-[Frame])/[OP]/100*[Proportion])

and then somehow perform the calculation rather than just look it up.

The main reason is it would be so much easier to see what is being calculated rather than try and weed my way through nested IIf statements.

Can anyone shed any light on this or maybe even a better way to accomplish what I am after?

View 1 Replies View Related