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.
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.
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!
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.
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.
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!
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.
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.
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.
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?
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.
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.
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
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?
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.
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.
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"
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:
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"
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...
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".
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...
