Thanks for the help. I'm getting through this db.
I am trying to get user input of start date and End date for the following:
SELECT [ASC Cust Count_Crosstab].Node, [Oracle Account TC].[Q Code] AS [Q5 TCs], [ASC Cust Count_Crosstab].Video, [Oracle Account TC].[Check In Date]
FROM [ASC Cust Count_Crosstab] INNER JOIN [Oracle Account TC] ON [ASC Cust Count_Crosstab].Node = [Oracle Account TC].Node
GROUP BY [ASC Cust Count_Crosstab].Node, [Oracle Account TC].[Q Code], [ASC Cust Count_Crosstab].Video, [Oracle Account TC].[Check In Date], [ASC Cust Count_Crosstab].ASC, [Oracle Account TC].[Q Code]
HAVING ((([Oracle Account TC].[Q Code])="5") AND (([Oracle Account TC].[Check In Date]) Between [Start Date] And [End Date]) AND (([ASC Cust Count_Crosstab].ASC)="uh"));
But I get a reply back "The Microsoft Jet database does not recognize '[Start Date]' as a valid field name or expression.
Could someone tell me what I'm doing wrong. They are date fields. Thanks.
I have a main form TRequests, which has several subforms, although we are dealing with two here in particular.
I have one subform [bsdata] that has combo's to choose data and a [enter] button to submit.
This updates the second subform (query table) with a [PK] and further metadata...
I would like to have the [enter] key from the first subform, also open a pop-up [addnew] (no biggie) and enter the newly created [PK] in a field that I normally have to enter it in manually.
I would like to accomplish this before the data updates to the query table showing the results.
I created a fixed header cross tab query that totals up how many photos of each size there is in an order. I wrote some visual basic code to total up the price (kind of complex with the different packages) but any orders without at least one of every size photo completely locks up Access. The following is the code:
Private Sub Text66_DblClick(Cancel As Integer) Dim Units Dim UnitsCache Dim PackagesCache Dim F4x5Cache Dim F5x7Cache Dim E8x10Cache Dim E11x14Cache Dim S16x21Cache Dim T21x24Cache Dim WalletsCache Dim T4x5 Dim T5x7 Dim T8x10 Dim T11x14 Dim T16x21 Dim T21x24 Dim TWallets
If [4x5] = Null Then T4x5 = 1 Else T4x5 = [4x5] End If
If [5x7] = Null Then T5x7 = 1 Else T5x7 = [5x7] End If
If [8x10] = Null Then T8x10 = 1 Else T8x10 = [8x10] End If
If [11x14] = Null Then T11x14 = 1 Else T11x14 = [11x14] End If
If [16x21] = Null Then T16x21 = 1 Else T16x21 = [16x21] End If
If [21x24] = Null Then T21x24 = 1 Else T12x24 = [21x24] End If
If [Wallets] = Null Then TWallets = 1 Else TWallets = [Wallets] End If
RerunPackages: PackagesCache = "x" If [T21x24Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then PackagesCache = "F" [WalletsCache] = [WalletsCache] - 2 [UnitsCache] = [UnitsCache] - 4 [T21x24Cache] = [T21x24Cache] - 1 Text58 = Text58 + 439 GoTo FoundOne End If
If [S16x21Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then PackagesCache = "E" [WalletsCache] = [WalletsCache] - 2 [UnitsCache] = [UnitsCache] - 4 [S16x21Cache] = [S16x21Cache] - 1 Text58 = Text58 + 305 GoTo FoundOne End If
If [E11x14Cache] > 0 And [WalletsCache] >= 1 And [UnitsCache] >= 3 Then PackagesCache = "D" [WalletsCache] = [WalletsCache] - 1 [UnitsCache] = [UnitsCache] - 3 [E11x14Cache] = [E11x14Cache] - 1 Text58 = Text58 + 236 GoTo FoundOne End If
If [WalletsCache] >= 2 And [UnitsCache] >= 4 Then PackagesCache = "C" [WalletsCache] = [WalletsCache] - 2 [UnitsCache] = [UnitsCache] - 4 Text58 = Text58 + 169 GoTo FoundOne End If
If [WalletsCache] >= 1 And [UnitsCache] >= 3 Then PackagesCache = "B" [WalletsCache] = [WalletsCache] - 1 [UnitsCache] = [UnitsCache] - 3 Text58 = Text58 + 127 GoTo FoundOne End If
If [WalletsCache] >= 1 And [UnitsCache] >= 2 Then PackagesCache = "A" [WalletsCache] = [WalletsCache] - 1 [UnitsCache] = [UnitsCache] - 2 Text58 = Text58 + 89 GoTo FoundOne End If
FoundOne: If PackagesCache <> "x" Then If Text56 = " " Then Text56 = PackagesCache GoTo RerunPackages ElseIf Text54 = " " Then Text54 = PackagesCache GoTo RerunPackages ElseIf Text52 = " " Then Text52 = PackagesCache GoTo RerunPackages End If End If
UnitsCache = (T8x10 + T5x7 2 + T4x5 4) - UnitsCache Do If F4x5Cache < 4 Or UnitsCache = 0 Then GoTo Skip4x5 End If F4x5Cache = F4x5Cache - 4 UnitsCache = UnitsCache - 1 Loop Skip4x5: Do If F5x7Cache < 2 Or UnitsCache = 0 Then GoTo Skip5x7 End If F5x7Cache = F5x7Cache - 2 UnitsCache = UnitsCache - 1 Loop Skip5x7: Do If E8x10Cache < 1 Or UnitsCache = 0 Then GoTo Skip8x10 End If E8x10Cache = E8x10Cache - 1 UnitsCache = UnitsCache - 1 Loop Skip8x10:
Text42 = 0 Redo: If WalletsCache >= 5 Then Text42 = [Text42] + 40 WalletsCache = [WalletsCache] - 5 GoTo Redo End If If WalletsCache = 4 Then Text42 = [Text42] + 37 ElseIf WalletsCache = 3 Then Text42 = [Text42] + 33 ElseIf WalletsCache = 2 Then Text42 = [Text42] + 28 ElseIf WalletsCache = 1 Then Text42 = [Text42] + 18 End If
I have built a query to calculate the expiry dates of training courses but I am trying to input a criteria so that only dates within 90 days of todays date show. I am using Date()<90 but it doesn't return the correct information. What the criteria should be for this?
I have a table of records, which has within it two date fields (effectively, a 'start' and 'end' date for that particular record)
I now need to create a query to perform a calculation for each date between the 'start' date and the 'end' date
So the first step (as I see it anyway) is to try to create a query which will give me each date between the two reference dates, in the hope that I can then JOIN that onto another query to perform the necessary calculation for each of the returned dates.
Is there a way to do this?
So basically, if for a particular record, the 'start' date is 01-Apr-2015 and the 'end' date is 09-Apr-2015, can I produce a dataset of 9 records as follows :01-Apr-2015
(The *obvious* solution would be to create a separate table of dates, from which I could just SELECT DISTINCT <Date> Between #04/01/2015# And #04/09/2015# - but that seems like a dreadful waste of space, if that table is only required to generate the above? And it would have to cover all possible options; so it would either have to be massive, and contain every possible date - ever! - or maintained, adding new dates as necessary when they are required. Seems horribly inefficient!)
Is it possible to just select each date between the two reference dates? Or can you only query something which exists somewhere in a table?
I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.
One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.
This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.
What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?
I have two tables with dates. Between (!) every two following dates in table1, I want to know the number of dates in table2. How do I write an SQL query for this? The tables I have are up to a few hundred records in table 1 and a few thousand records in table2. So to prevent that this takes hours I need a fast query.
To explain the query I need, for example: table1 01/01/2014 15/01/2014 17/01/2014 30/01/2014
Explanation: Between 01/01/2014 and 15/01/2014 in table 1 there are 2 dates in table2 (01/01/2014 is not included between the dates) Between 15/01/2014 and 17/01/2014 in table 1 there are 0 dates in table 2 Between 17/01/2014 and 30/01/2014 in table 1 there are 4 dates in table 2
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100 Matt 15/5/11 15/9/11 7/7/11 £200 Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
I have a client that wants to enter a range of dates in a query of when they will call that person back. Then they want to be able to type in a range of dates and have a make table query show them all the people that fall in between these two dates....is this even possible??? Ex. Joe March 3 to March 8 Mary March 4 to March 9 John March 5 to March 10
So if they type into the query March 3 to March 6 all three people should show up because one of the dates specified lies within the parameters they are asking for.....man I am out of ideas
I want to add Hours to a date value. For example the date value=05/04/2005 18:12:35 I want to add three hours to that date value so the new date value will be 05/04/2005 21:12:35. Is there operatior to add dates.
Set db = CurrentDb() str = "SELECT ID, Date, Item, QtyRec, QtyIssue FROM DailyIssue Where Date >= '" & SDate & "' AND DATE <='" & Edate &"'" Set rs = db.OpenRecordset(str, dbOpenSnapshot)
But it is not working at all. Error message is Data Type Mismatch.
i've been reading about the us/uk date problem and found some helpful threads such as http://www.access-programmers.co.uk/forums/showthread.php?t=39675 but would like to ask (cause i'm still a bit confused):
if someone enters all of the dates into an .mdb the same way, either day-month-year or month-day-year, will the dates somehow be stored correctly regardless of the system's setting? (in this case, entered day-month-year into u.s. system-settings).
i have seen how access flips "wrong" dates. if i understand correctly, the dates are then actually stored "flipped", or wrongly.
is there some way of making the wrong dates (the ones that have been flipped) right again?
also, how does one view the dbl-precision number that is stored?
I capture last change date on a form using now(). When I export the file my vendor wants the date to be a text field and 8 bytes in length. It has to fill 8 bytes like this - YYYYMMDD
Example in my form - 3/1/2007 8:11:55 AM
I need to convert this to text - 20070301 when I export my file.
I have a date field in a table that I store dates in UK format, (DD,MM,YYYY). I have aform with a command buttong that is supposed to open another form at a particualr record based on the date field. However if the day part of the date field is over 12 then no record is returned. For example when trying for 02/11/07 I am assuming Access is looking for 11th Feb 2007. How do I solve this?
how would i set criteria on a query between 2 dates? i need to run a report every month between the dates 4th of current month and 5th of previous month. i wanted to either run this in code or set the criteria on the query but by setting the query criteria, it needs to pick up the current month.
a further question on this would be-
how would this work in 12 months? would it run the same query and collect 2 years worth of info or can it set with years too?
i was planning 2 options.
quick report ( click button and this months report is generated ) select report ( where they can select their desired period )
the dates will ALWAYS be the same and no other dates will be required.
I'm sure this is really easy - the database I am writing is for sales enquires with an anticipated completion date (month), but I also need to show which year. I would then want to concatenate these two fields and be able to produce a report that would list by date in ascending order. Can someome tell me the correct way to do this. Should the fields be text or date fields? Thanks in anticipation.
1st Field is called Termination Date 2nd Field is called Termination Notice-Due Date:
The Termination Notice-Due Date has an expression builder i.e. Termination Notice - Due Date: [Termination Date]+28
This then gives me a Termination Notice - Due Date Automatically filled in using the above expression but it counts saturdays and sundays i dont want saturdays and sundays counted in the +28
I have a field in a table with dates in it. I want to set up a query and pull down the date field twice, and want the first to show just the earliest date and the second to show the latest date. Is there a way of doing this.
