I have a db for a list of people who have attended courses.
Each year they have to attend a refresher.
I can easily query those people who are in date.
How do i query to find people who attended a course but not a refresher?
Thanks
Hey.I know you can create queries in access to find fields that have a value between 3 and 7 in them for example but how would you create a query that retreives fields with values between 2 dates for example between 12/10/07 and 11/01/08Btw the dates are inday/month/year
View 1 Replies View RelatedI'm trying to make changes to records that are 3 years old. The date format is text and it displays as Ex. "20020831" or yyyymmdd.
i try <"20020101" but I get no results and I know there should be.
I'm a rookie and only know how to query using short date format.
ideas anyone?
thx
I have linked tables from an Oracle database.
I want to run a query to find records that have dates in a defined range.
The date field in my linked Oracle table is in the date/time format.
When I run my Access query, I only get those records that have a date (and no time) in the field.
How can I get all records, even those with a date/time entry?
Thanks!
I have a db tracking vacation times for staff. One of the fields tracks a members vacation start and end dates. I need a way to pull a report to see who is on vacation based on Date() (today).
For example:
A record for John Doe has him start vacation 08/19/2014 and end vacation on 8/28/2014. If I wanted a report that shows who is on vacation today, 08/25/2014, using the date() function), how would I do this?
After several days of searching, I haven't been able to find any threads related to this. I'm making a search form that queries a pdf library table. Once a search query is entered, the user is able to open the files from a results form. Currently, I am entering the filenames from the network directory manually into the pdf library table.
For example, I manually enter the following information into the tblPDFLibrary table that contains the fields:
Date added, Filename, File Path, Series, Class, Title.
I was wondering if there's a way to have Access query all the filenames in the directory for me. In other words, if the directory contains the filenames: AccountsPDF, InventoryPDF, CustomerPDF. Can I have Access query all the filenames within the directory and automatically add all the file names (AccountsPDF, InventoryPDF, CustomerPDF) to the Filename field within the tblPDFLibrary table? If I am able to do this, I can code the other fields to populate information because the filename contains all the other field information (except for file path but I can program it insert the UNC path).
Is there a way in this program to create a list of dates between 2 dates?
i.e I have Arrival Date and Departure Date. Is there a function or expression that will list all the dates on and between?
Hey
I need to find Customers who have not had a meeting for over three months and i need to list them in a query, please could someone help me as this is getting very frustrating
I have a table containing a list of dates in "MMM YYYY" text format from "Jan 2010" to "Dec 2020".
I would like to restrict the list from the first record (Jan 2010) to the current month using today's date (eg in this case Dec 2013).
I know about
Code:
Format(Date(),"mmm yyyy")
And have tried the criteria
Code:
<Format(Date(),"mmm yyyy")
But doesn't work and haven't got much further....
I have a form and I need a combobox on the form to produce a list of Dates for every Saturday starting from the current day displayed in a short format for the user to select. Does anyone have an idea how I could manage that?
I was going to make a table and write in each one and use that for the rowsource, but someone said that there might be a way to do that dynamically.
I'm new to Access VBA and I am having a bit of trouble. I want to find the dates between 2 dates(both inputted into textboxes), when I press a button I want them dates found to be displayed in a listbox.
View 5 Replies View RelatedI want to make a query in my system which finds out if a room is available for a person to stay in. I want the query to show me a list of rooms that are available between a period of two dates. So if I enter a arrival Date (12/12/12) and then a checkout date (13/12/12) the query then shows me a list of rooms that are free for those dates. How would I go around doing this?
View 1 Replies View RelatedI am trying to add up a list of dates that match a criteria... a search box result.
I have tried DCount, and now I'm doing it through SQL, and no matter what combinations I try I still get an error - usually 3075 - Syntax error (missing operator)
But I can't find anything missing - I copy the SQL into a query view and it works perfectly... but it won't work on its own. And I've tried using DCount with the query as a query object, and I get the same error.
Code:
Dim ResultCount As Long
Dim DateSearch As Date
Dim MyDate As String
Dim MyDateAdd As Date
Dim varReturnValue As Variant
[Code] .....
I've used the >= And < option as it solves an issue with Date Time. What operator is missing!?
I am making a Car Rental database on Access 2010, and I have a list of the dates available to rent a car in a table. I want to be able to check what dates are available and select a date for hire in a form.
View 3 Replies View RelatedI've been asked to get some information from my database and I'm a bit stuck.
I have a list of refunds in tbl_main and each one includes a dateReceived. I make a record in either tlk_located, tlk_unableToLocate or tlk_bulk depending on the outcome when we're trying to send the money back to whoever it belongs to. Each table has a time stamp (named locatedTime, unableTime and timestamp respectively) field
My manager wants me to report how many entries were unworked on each day in the year, and what the value of them was. An entry is unworked if there is no entry in either of the 3 tables.
So I need a query that lists a range of dates, and for each date counts the number of entries where tbl_main.dateReceived is <= to that date and either has no record in located,unable or bulk or has a record with a timestamp > than the date. (It has been processed now, but hadn't been on the date we are looking at)
I can manage a query that looks at a certain date that it prompts for on each run:
Code:
SELECT Count(tbl_main.trust2PK) AS CountOftrust2PK, Sum(tbl_main.amountRefunded) AS SumOfamountRefunded
FROM ((tbl_main LEFT JOIN tlk_located ON tbl_main.trust2PK = tlk_located.trust2FK) LEFT JOIN tlk_unableToLocate ON tbl_main.trust2PK = tlk_unableToLocate.trust2FK) LEFT JOIN tlk_bulk ON tbl_main.trust2PK = tlk_bulk.trust2FK
WHERE (((tbl_main.dateReceived)<=[cutoffDate]) AND ((tlk_located.locatedTime) Is Null Or (tlk_located.locatedTime)>[cutOffDate]) AND ((tlk_unableToLocate.unableTime) Is Null Or (tlk_unableToLocate.unableTime)>[cutOffDate]) AND ((tlk_bulk.timeStamp) Is Null Or (tlk_bulk.timeStamp)>[cutOffDate]));
I would like a query that lists all dates in a range, and shows the same information for each day listed.
Hi I'm having a bit of a problem with the sql query below
SELECT [Table 1 Personal Details].Surname, Sum([Table 3 Daily Activity].[Public Holiday]) AS [SumOfPublic Holiday], [Table 3 Daily Activity].[Week Commencing], Sum([Table 3 Daily Activity].[Authorised Absence]) AS [SumOfAuthorised Absence], Sum([Table 3 Daily Activity].Holiday) AS SumOfHoliday, Sum([Table 3 Daily Activity].Sickness) AS SumOfSickness, Sum([Table 3 Daily Activity].[Unauthorised Absence]) AS [SumOfUnauthorised Absence], Sum([Table 3 Daily Activity].[Hours Per Week]) AS [SumOfHours Per Week], [Table 9 Funding].Project
FROM [Table 1 Personal Details] INNER JOIN ([Table 3 Daily Activity] INNER JOIN [Table 9 Funding] ON [Table 3 Daily Activity].[ID Number] = [Table 9 Funding].[ID Number]) ON [Table 1 Personal Details].[ID Number] = [Table 3 Daily Activity].[ID Number]
GROUP BY [Table 1 Personal Details].Surname, [Table 3 Daily Activity].[Week Commencing], [Table 9 Funding].Project
HAVING ((([Table 3 Daily Activity].[Week Commencing]) Between [Enter Start Date] And [Enter End Date]) AND (([Table 9 Funding].Project)="NETWORKS"))
ORDER BY [Table 3 Daily Activity].[Week Commencing];
My problem is that the Surname is displaying for each record.. what I mean is that I want the surname to be grouped so that I get the SUMofHours summed correctly?
here's an example of what I'm getting
SURNAME, WEEK COMMENCING, SUM OF HOURS, PROJECT
Brown,13/02/2006,35,NETWORKS
Brown,20/02/2006,35,NETWORKS
Malcolm,13/02/2006,35,NETWORKS
Malcolm,20/02/2006,35,NETWORKS
Here's what I'm really looking for though
Brown,13/02/2006,70,NETWORKS
Malcolm,13/02/2006,70,NETWORKS
I need the Hours SUMMED with no replication of surname.
Thanks
CJ
Thanks
CJ
Hey all,
Here is my problem, im working on an old 97 database that is being used by a program and sometimes errors occur through the use of the program and certain tables are not updated properly, so instead of doing this manually I want to be able to query this database through SQL and then later down the line put these SQL queries into a program so it will be more user friendly to edit the database.
So when I try and test one of my queries out, access tells me that I cant make changes to the database because it was created in an earlier version. Now I know i can update the database, but if I were to do that the program that is using the database wont work!
So im a little stuck now, will I have to keep editing the database the manual way or is there another option here? Im wondering (for any java heads out there) If I were to write a java program that would query the database and edit the data inside would that work or would I get back an error?
Thanks in advance!
Ok, I have a DB of client contacts. I'm drawing the values for a query from a form using [Forms]![Sort]![Contact] in criteria and using the parameter field to give values to each. The query works fine for state, contact and industry- all in conjunction with each other. Where I am having problems is:
1) When I input less then all 3 working parameter values in the referenced form the query will not run. I think i need an SQL string of some sort- doing an if, then relationship, but I don't know how to do this.
2) I don't just have 3 values I want to reference, I have 9 total. On the form one of the problems is that a field is chosen by the end-user using check boxes (allowing only one check between three values, with no default: current client, potential client, not applicable are the choices). However, in the table these are referenced by the values of 1, 2 or 3. Another of my fields is check boxes, but referenced by yes, no. How do I correspond the field values in text to numerical values in the table? (and the yes/no prbly the same I will assume)
3) I have "sectors" as a field option in my Sort form (again the drop down menu to select). There is one field for the choice, however, in my data table I have 4 fields corresponding all back to that one field in the form through the query. This doesn't seem to work at all. The sector choices, in my original Input form, are 8, chosen and recorded into the table using 4 consecutive drop downs. They are the same 8, so sometimes a sector like Agriculture could end up in the Sector 1, or Sector 2, etc field in the table. Some can also be left blank. So in summary of this dilemma: 4 drop downs in input form to 4 data table fields, all being queried through one field on the Sort form, which is tied to my query through a command button, having specific criteria reference text in the form.
Thanks in advance for any help!!!
Just joined this forum and posting my very first thread.. ^^
I need to create query to pull data from two different tables. This should be really easy query for most of you. Once someone help me set it up, I can do the modifying.
If you look at the image, there are two tables. For each of pf_id (which is unique ID), I need to know the dept_name which is on other table. I know two tables can be linked with dept_id but I don't know how to create query. If you can help me , I would greatly appreciate it~
Result should be like below.
pf_id, dept_id, dept_name
___________________________
wh08051bh-Navy, 01-001, washed hat
wh60516bh-white, 01-001, washed hat
.
.
.
ru11072bh-white_with_navy, 01-002, roll up bucket hat
.
.
dh03871bh-Dk._Blue, 01-003, denim hats
.
.
<img src="http://www.highdots.com/forums/attachment.php?attachmentid=183">
http://www.highdots.com/forums/attachment.php?attachmentid=183
Is there a way of querying for an astricks? Some data in one of my tables has data that looks like this "A*" or "B*" or "C*", but most of the records are just "A" or "B" or "C"...I just need a query that I can pull out those records. Is there a way of doing this in a query?
Thanks
I need to take a value from a subform and use it in a query but I don't seem to be able to come up with the correct criteria in the query....
I have so far tried:
[Forms].[frm_Matching].[SubMatching].[txtSingleFixedPayer]
[Forms].[frm_Matching].[SubMatching].[form].[txtSingleFixedPayer]
[Forms].[sub_Matching_single].[txtSingleFixedPayer]
[Forms].[sub_Matching_single].[form].[txtSingleFixedPayer]
[Forms].[frm_matching].[sub_matching_single].[txtsinglefixedpayer]
[Forms].[frm_matching].[sub_matching_single].[Form].[txtsinglefixedpayer]
And probably a load of other versions of the above.... can anyone help out possibly?
The following pulls back the value in code but isn't accepted in the query as a valid criteria:
Forms("frm_Matching").[SubMatching].[Form].[txtSingleFixedPayer]
OK,
I work for a political party. I work with voter history information. Normally, I am doing queries in order to bring up particular voters, and eliminate others.
Example - the basic table I work with contains the names, addresses, affilitations, and voting histories of everyone in my county. I normally query to bring up voters of a particular party, or people who voted in a particular election or number of elections or both. I more or less sort voters out, but in a positive manner, by creating tables with voters who did a particular thing.
For example: out of all the voters, I just want Democrats who voted in two of the past three primaries. I would then get a table that contained just those voters and no others.
Today, though, I was asked to create a table that would exclude voters based on their behavior, exluding voters who did a particular thing but showing everyone else.
Example, I have a request for all of the voters in the county, except those who voted in the 2004 and 2002 primaries. I know how to create a table with those voters and no others, but I don't know how to query for everyone but those voters. Can anyone help?
Hi there,
I am getting really wound up with this. It should be so simple!
I need to create a query on a field (actualdateofvisit) and group it by the month to give me a count of all those visits (hence the catchy field name) which occurred each month since the beginning of the project. In other words I want to see:
Month Count of Visit
Jan 43
Feb 54
Mar 78
and so on.
I have tried everything I can think of but cannot work out how to do this? I need to do it in a query NOT a report as I want to port it to Crystal reports to display there.
I am think of something like:
field: Actual Date of vist
table: visits
total: Group By
Criteria: "Month"
Can anyone help me?
Thanks and Regards
Mark
Hi all,
I have a query that is supposed to get a time value from a field and enter a string into a new field based on the time it gets.
this is what i have. Its my VERY first query ever and so i'm not sure of the syntax. I doesnt work though so i'm assuming something must be wrong. Can anyone help?
iif((hour([request date])>12)and(minute([request date])>30)), "After 12:30", "Before 12:30"
Cheers,
Spinkung. :)
Hi all,
Is it possible to create a query that looks over two different databases for information. i.e I have two departments at work that have seperate databases and I need to be able to search for data in both of them on the same query.
Any help would be greatly appreciated.
Hi, I'm having some issues querying a sub form.
I'm using a combobox (in a subform) which supplies some search criteria. I'm using the query Like [Forms]![Employee]![Course Subform].[Form].[Combo12] & "*" but its simply not working.
Can anyone see any glaringly obvious mistakes?