I am trying to do a date query with conditions in excel but it don't seem to work.
I am trying to do a query on a table on the birthdate column and I need the records of all athletes that were born on or after 1/1/2008. Below is what I did but it doesn't seem to work.
Created a design view
chose the birth date field
and the criteria row I typed
>=#1/1/2008#
I am so frustrated right now that I could scream. I have a database which tracks attendance and referrals for a networking organization - it meets weekly on Thursdays. It has a report which displays totals of both attendance and referrals for each weekly meeting for an entire month. The problem I am having is related to the fact that some months have 4 Thursdays and some have 5. If I get my queries to display the meeting dates correctly for the 4-Thursday months, then it displays incorrectly for the 5-Thursday months, and vice versa.
Is there any way to put a criteria on a missing record? If I could have it just populate the 5th week with spaces or null when it doesn't exist for that month, it would make my life a lot easier.
I can send a copy of the database to anyone who thinks they can help. It's too big to post.
Hi friends,
I had two tables parent(f1) , child(f1,f2,f3).
I had to display f3 value for parent(f1) when f1 has only one f2,
if f2 values are more than one for single f1 and if all f3 values are 0 then
i should display f3 as 0, else if some values for f3 are nonZeros and some are Zeros then
is should display f3 value as '-'.
How to do this?
please any suggestions?
Thanks.
I have a filed and I want to find out its length then in the next column i have to add the data like if filed 1's length is 1 then it should be 000+field1.value, if it is 2 then 00+field1.value, if it is 3 then 0+field1.value and so on...any help is appreciated , thanks
View 2 Replies View Relatedi want to write a ms access query for date difference.i have the excel formula for that.in attachment i have shown wot i really want as output.i want to make a IIf query for these conditions.
in excel i did this like:
=IF(J13="",IF(INT(B13)=INT(AE13),C13-DAY(I13)+1,C13),IF(INT(AA13)=INT(B13),IF(MONTH(J13 )=MONTH(I13),J13-I13+1,DAY(J13)),IF(INT(AA13)=INT(D13),DAY(J13)-E13,0)))
how to do it in access query.
I'm trying to get this query to return all records except those with category: 4 AND issue: 12
Instead I'm always getting ZERO category: 4 and ZERO issue: 12
Should not the parenthesis force this to be taken as a single "WHERE"?
SELECT ASSIGNED_TO, COMPLAINT_NUM, DATE_RESOLVED, CATEGORY, ISSUE
FROM tmptbl_tm_YTD
WHERE (CATEGORY<>4 AND ISSUE<>12);
For some reason, this one is driving me nuts so i would really appreciate some help.
This is kind of complicated to explain but here goes.
I am trying to create a query so that, based on certain conditions certain rows from the Form/Table are either visible or not visible on the report.
I have four fields involved:
Antic Dep$ (Anticipated Deposit Amount)
Antic Loan$ (Anticipated Loan Amount)
Date Dep Booked
Date Loan Booked
Basically i am trying to hide any records that in which the Date Dep Booked and/or Date Loan Booked are older than the current month...
I can think it through logically but cannot get my head around the query.
If Date Dep Booked or Date Loan Booked is not older than current month then show the row;
If Date Dep Booked is older than current month and Date Loan Booked is older than current month then hide the row; unless
If Date Dep Booked is older than current month but Date Loan Booked is not older than current month then show the row; except
If Date Dep Booked is older than current month and Antic Loan$ is zero then hide the row; but
If Date Dep Booked is older than current month and Antic Loan$ is not zero then show the row;
Then i also need to do the reverse for the Date Loan Booked, i.e.
If Date Loan Booked is older than current month but Date Dep Booked is not older than current month then show the row; except
If Date Loan Booked is older than current month and Antic Dep$ is zero then hide the row; but
If Date Loan Booked is older than current month and Antic Dep$ is not zero then show the row;
Phew! That probably makes no sense but i had to try.
Once again, i appreciate any help so that i can keep the remainder of my hair.
Switters
I'm having a rough time trying to figure out how to pass a date to an SQL statement that Excel VBA macro will run. The date is in a cell (A1) formatted as 'm/d/yyyy'. Let's say it's 2/1/2014. I want to run an SQL statement that retrieves data from a table where a field is greater than 'A1'. The table field is a date/time field and has values formatted as 'mm/dd/yyyy'.
I've tried various syntax on the Where but cannot get it to work.
sd = Range("A1")
SELECT [tn].[Date Submitted]
FROM[tn]
WHERE tn.[Date Submitted] > """ & sd & """
This results in the following where clause that does not work.
WHERE tn.[Date Submitted] > "2/1/2014"
Ok i'm new so don't be too hard.
I'm making the most simple DB where there are bookings consisting of a StartDate and EndDate. The Scenario is a car hire firm. Obviously i don't want to be able to double book a car and want to be able to list all cars available within the two dates (input).
I have set the defult input to 12/07/04(StartDate) and 12/17/04(EndDate) for the purpose of testing.
I have created a query to find all cars which are not available in relation to the input dates, but cannot get it to list the available cars!
this is the query i have:
-----------------------------------------------------------
SELECT CarDetails.CarReg
FROM CarDetails INNER JOIN BookingDetails ON CarDetails.CarReg = BookingDetails.CarReg
WHERE (((((([BookingDetails].[StartDate])<#12/7/2004#) And (([BookingDetails].[ReturnDate])<#12/7/2004#)) Or ((([BookingDetails].[StartDate])>#12/17/2004#) And (([BookingDetails].[ReturnDate])>#12/17/2004#)))=False));
-----------------------------------------------------------
All i want to do is take all of the cars and deduct the one's which are produced by this query.
Thanks for any help!
I have a parameter query with a totals row that displays averages. Is there a way to have the average row use only specific records in its calculation based on one of the field's values WHILE still displaying all the records returned by the query.I want only data that has a "YES" value used in the average while still displaying the records marked as "NO"
View 1 Replies View RelatedI have an excel file linked to a table in Access. Several fields are date data types in excel but are showing up as text fields in Access.
My real goal is to do a comparison between two tables, but only if the date of the one piece of data is newer than the other. I had planned on comparing the two date fields but even though I have formatted the date fields in my excel file to be "Date", when I look at the design view of my table it is showing up as "Text" and therefore I am unable to do this comparison.
I'm not sure if it's just something that I'm missing but maybe someone else knows an easy fix to this. I know this is probably a simple question, but I did search the forum and didn't find a thread that specifically dealt with this issue.
Thanks in advance for your help.
I want to create a calculation query that uses different equations under certain conditions. Here's specifically what I need:
If the "Cost_Category" field is "Full Price" then the query uses the following calculation:
Total_Cost: Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost]))
If the "Cost_Category" field is "BOCES" then the query uses the following calculation:
Total_Cost: Sum(nz([BOCES_Number_of_Participants])*nz([Cost_Per_Person]))
I have successfully created these two queries individually, but combining them doesn't seem to work. Here's what I wrote:
Total_Cost: IIf([Cost_Category]=Full Price,Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])),
IIf(Cost_Category]=BOCES,Sum(nz([BOCES_Number_of_Participants])*nz([Cost_Per_Person]))
It keeps coming up with errors, saying that I misplaced a comma, parenthesis or quotation. I've tried playing with it, changing the syntax slightly but it doesn't seem to work.
Iād like to repeat the last not = 0 record under certain conditions, in a table or in a query.What I have:
Calendar_All Dates
Calendar dateMyValue
7/6/20140.00
7/7/2014108.94
7/8/2014107.71
[code]...
Basically, if Calendar date > Date(), if Calendar Date not Saturday or Sunday, weekday(Calendar date<>1 and <>7), AND Calendar Date not in (Holidays table).Then repeat the last not = 0 value of MyValue. I thought of changing the default value but the value is already 0, while default is null + I need to set the default value under certain conditions.
I am using Excel and Access 2010.
I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.
My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.
My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.
My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?
Hrmm, im sure there used to be an Excel forum, i seem to have lost it :/ Hope its ok posting here:
What im trying to do is, for a range of dates from 1/1/2005 to 31/12/2020 get the Qtrly Financial Period before...
So, for instance:
A date of 1/1/2005 makes the period before 1/10/2004 to 31/12/2005
A date of 1/5/2005 makes the period before 1/1/2005 to 31/3/2005
A date of 1/8/2010 makes the period before 1/4/2010 to 30/6/2010
Fiscal Period (for those that dont work with them) are, 1st April to 30th June, 1st July to 30th Sept, 1st Oct to 31st Dec and 1st Jan to 31st March of any year.
So what i want is 3 colums in excel, Column 1 = Period Start, Column 2 = Period End, Column 3 = Date
Can anyone figure the forumla out? Doesnt matter where its done in, just as long as the end result can be in Excel....
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 ....
I am trying to write a code that will enable an event to happen if 2 conditions are true, something like:
If [DueDate] >= [CurrentDate] And [EndDate] is Null Then
Event ..... take place
I tried above and did not work
it works only if write one condition of above
how can I set it up correctly
I currently have a query which takes its criteria from a form called search. This all works fine, unless the user leaves a field in the search form blank. Then the query has no results.
What I'd like is for a blank field to act as a wildcard.
I guess the reason this doesn't automatically work is that Access (effectively) puts " & " around the criteria taken from a form, so the criteria ends up as " " not (there's a space there!).
I tried entering a criteria in the query that says:
IIF([Forms]![Search]![Date] Is Null,*, [Forms]![Search]![Date]
this doesn't work. For one thing Access edits the * into "*", so it is no longer a wildcard. Secondly, I've never used IIF before, so it's probably wrong anyway...
So, can anyone help me do this?
Even if a blank field cannot be used as a wildcard, any character would do...
Hi
Hope someone can help with this. Had a search through the forums but nothing quite the same (although I'm guessing it's a simple one to solve.)
I need to calculate a figure based on 2 criteria. The permutations are as follows
If condition A is less than 20 and condition B is No then return 9
If condition A is less than 20 and condition B is No then return 12
If condition A is greater than 20 and condition B is No then return 13
If condition A is greater than 20 and condition B is No then return 15
Any suggestions?
Hi, I have created a Yes/No attribute. Im just wondering if a text boxes visibility can be dependent on the result of this. E.g. If a row is set to yes then the text box will be shown and if it is set to no the text box is made invisible. Thanks for any help
View 7 Replies View Relatedim trying to import the attached excel file to access table. I got error message says "type conversion error". the date column after 07/13/2007 can not be imported, but anything before that date can. I formated everything in excel with the same format, how did this happen?
Thanks.
I have an Excel workbook which I use to extract data from an Access database using VBA. A requirement has popped up to show things from the last week. Unfortunately, I can't just use a Date()-7 query in Access as the start point might be different, so it needs to be based on seven days prior to a date field in Excel.
So, I have a cell in Excel with a date, in this case "16/01/2014". I want to then use that date in my query to say "greater than 8 days prior to to this date". The field is formatted as a date and the variable (DatafileDate) taken from this field and passed to the function is a Date. In my Select query I then have this line to select the top 5 marked as UK accounts in this case:
Code:
SearchText = "SELECT TOP 5 * FROM `" & TableName & "` WHERE `" & TableName & "`.`Account`='UK' AND `" AND `" & TableName & "`.`Close Date` > #" & DatafileDate & "#"
Code:
#" & DatafileDate & "#" -8
Should mention that the TableName stuff is so the name of the table can be set elsewhere. "Close Date" is the column in my Access query I need to filter on.
I have in a cell in Excel: MM/DD/YYYY...I want to add it to my Access database as: YYYY/MM/DD...This is what I have so far in Excel VBA. It is giving me a date error. I have tried both as MM/DD/YYYY still did not work.
Code:
n=1
accDateSub="01/01/2011"
Dim conn As New ADODB.Connection
conn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:pathfile1.accdb;"
thisSQL = "INSERT INTO [Table1] VALUES (" & n & ", #" & Format(accDateSub, "yyyy/mm/dd") & "#);"
conn.Execute CommandText:=thisSQL
Hi again :)
Since I am new to Access I am having a problem figuring out how to set the participation conditions (optional or mandatory) for my relationships. I have looked through MS access online and forums, google etc. and found nothing.
I would be very grateful for any help :)
Kind Regards
Marie
The following SQL statement with 1 where condition works fine, but the Next SQL statement with 2 where conditions does not work, a error message says Run-Time error 13 type mismatch , what is incorrect in the statement when the AND MinID = 15 is added. The table data type in set as number
SQL = "UPDATE MinistryInv2 Set MinID = " & 14 & " WHERE PID = " & Me!ID & ";"
SQL = "UPDATE MinistryInv2 Set MinID = " & 14 & " WHERE PID = " & Me!ID And MinID = 15 & ";"
Phil
I have set up a querry that gathers information from different tables:
ID | Name | StartDate | EndDate | NbOfDays |
Then, i am using a form to fill up this querry. Normal stuff.
1- How and where can i set a condition so the input of EndDate could not be smaller than StartDate?
2- How and where can i set up and write the formula that calculates the NbOfDays? (meaning number of days worked form start date to end date)
Thank you for helping!