I have a Qry1 that contains a criteria of:
Between [Start Date] And [End Date]
I eventually use a crosstab Qry2. The wizard does just fine.
but when i run the crosstab, i get an error message:
"the microsoft Jet engine does not recognize "start date" as a valid field name or expression."
Is there anything i can do?
Penwood
I am trying to create a user-friendly database to enter invoice records (deductions and additions) for securities. I would like to be able to run queries with running totals from month to month inclusive (end total of one month = starting total of next month). I have already figured out how to create a running total; however, I have numerous "companies." Each record could be from any of 30 companies. When I add up the running total from say January 1 to February 28th, I get the additions and deductions of all the companies, not just the one I want. Inputting the companyID (example A110) in the query box only adjusts what is displayed, not the running totals that go by the total deductions from the records before that record (I assigned each record a "database ID" because dsum does not recognize dates in the criteria box, if I want to do a running sum.
I also was wondering if it would be possible to build forms with macros to create these queries in a user-friendly manner (so the company that I am interning for can create queries when they need them without me there).
I upgraded to Access-2010 and the Text File Import function will not recognize dates in the format YYYY-MM-DD. The import dialogue sees enough to recognize the field as a date, but then every date encountered is written to the Import-Errors table. This is true whether the file has a .txt or .csv extension. The actual file format is .csv.
View 6 Replies View RelatedHello,
I have a crosstab query with the following column which calculates the number of days an employee took to perform a task. Problem is that it is also counting weekends and holidays.
Field: LogDate
Table: tblWorkLog
Total: Count
Corsstab: Value
Is there anyway to exclude weekends and holidays from that value?
I have a table, tblHolidays, with the field, HolidayDate. Is there a function that I can use like the WeekDay and use that with the HolidayDate field so that I can only get the Number of Work days?
***Addition***
Sorry I would also like to clarify something I have only one date field. What's happening is the user logs in and inputs what he did for the day, and the date is logged.
So the crosstab query sums up those days, but I would like to exclude weekends and holidays.
I print a report based on Query1, with the selection criteria for dates being taken from 2 fields on a form. The selection criteria reads:-
>=[Forms]![DTdates]![StartDate] And <=[Forms]![DTdates]![EndDate]
Simple and it works fine.
I now need to base a second report on a Crosstab query based on Query1. Follow me?
Now, I have tried using the selection criteria in both Query1 and in Crosstab_Query1 and I get the error message:- "The Microsoft Jet database does not recognise 'Forms!DTdates!StartDate' as a valid field name or expression."
How do I get round this or can I?
The bottom like is I want to fire off printing the report from dates input to a form.
Help please.
Hello,
I have this so far:
TRANSFORM Count(tblclientcontacts.ENTERED_BY) AS CountOfENTEREDBY
SELECT "Count" AS [ENTERED_BY+OFFICER1+OFFICER2+OFFICER3]
FROM tblclientcontacts
GROUP BY "Count"
PIVOT tblclientcontacts.ENTERED_BY;
However I need this to show data that is for a certain date. I have a field named CONTACT_DATE which has the date in this format: xx/xx/xxxx.
I have been messing with the above and havent had any luck trying to get the crosstab query to only query say from months 10,11,12 or even for month 10.
Anyone have any pointers for me or a direction I can look at.
I keep getting not part of a aggregate function error, or syntax error in my attempts.
Thanks.
I want to create a cosstab query with dates for the next 12 months accross the top as columns and employee names down the side as rows. I then want to populate with data showing what each person is programed to do under the dates. For instance trainer 1 is delivering training from the 23/06-30/06 then designing a course from 02/07-10/07. that type of thing.
I could represent an activity using a colour i.e. yellow for delivery, green for design etc.
Also do I need to create a table with all the dates? Is there a quick way of doing this?
I have generated a table using a crosstab query and it gives the information as below
12/10 12/11 12/12 13/01
Part 1 1 2
Part 2 4 4 4
Part 3 5
So basically part, qty used, year/month used - it works fine up to this point.
Now I want to either add more columns to the crosstab query to count number of times Part 1 was used in last 12 months & 24-13 months
Or
Create another query to do this.
But I want the count function to start counting from current month to last 12 months and from month 13 to month 24 (in another column).
The problem I am having is that this query will run every month and I don't want to update the column headers (in query property sheet) instead I want query to pick this up automatically.
I use the design view to generate queries.
Why can't I get this simple piece of code to work?
Code:
Private Sub txtDateBack_AfterUpdate()
' updates loan status
txtLoaned = 0
If txtDateBack = Null Then txtLoaned = 1
End Sub
Code:
Private Sub txtDateBack_AfterUpdate()
' updates loan status
txtLoaned = 0
If txtDateBack = "" Then txtLoaned = 1
End Sub
I'm designing a form which will display a lot of employee information in a tabular layout. Users need the option to update a lot of these controls, but I want them to do that on a separate form. I'm wondering if VBA can determine the position of a clicked button within the tabular layout.
So if I have a layout like this:
| label | textbox | button | label | textbox | button |
| label | textbox | button | label | textbox | button |
| label | textbox | button | label | textbox | button |
Can I have some code which determines that, for example, the second button across on the 3rd row is clicked and read the corresponding label?
I was planning to have each button just call a function passing an argument, but since all these buttons will be labelled "update" it could get a bit confusing if I need to re-arrange the controls. So I wondered if what I describe above is possible, and which VBA functions I would need to look into if so?
When enforcing referential integrity, does access get angry about capital letters?Or just in general, if an address gets put in with a capital letter in one instance and then without in another does it make a difference?
View 14 Replies View RelatedI have used the code below to send an email automatically when a new record has been created. However I recently added the If Then statement to the code as you can see. The problem is that the email wont send because it says "There must be at least one name or distribution list in the To Cc or Bcc box". It is most likely saying this because it doesn't recognize "IT" in the txtDeparment combobox. How can I get it to recognize IT in the department combobox? The reason I am using an If Then is because I will make it send to different email addresses depending on what is in txtDepartment by using ElseIf
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
If Me.txtDepartment = "IT" Then
.To = "my email"
End If
.Subject = "hi"
.HTMLBody = "hi"
.send
End With
I split my database. Then got the bright idea to rename them. Now when I launch the front end (which contains the forms, etc.), I get a message saying that it can't find my backed. I've tried renaming the back end back to its original name, but its still not working.
What is the best way to fix this situation? I was thinking about opening the front end, and re-importing the tables from the back end, or, breaking the links between the front end and back end. If I re-import, should I move stuff from the front end to the back end or vice versa?
Thank you in advance for your help.
I am trying to get a Case Statement to evaluate multiple conditions. Example: below when I get diagnosis code 20400 and the age_at_diagnosis is 40 the code is basically ignoring the second condition of the Case "And rs![Age_At_Diag] < 18". How do I get the code to recognize both conditions?
Code:
Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
[code]...
I'm struggling with a validation rule that almost works but not quite.
I want to restrict the values in a feild by characters between 0-9, A-Z, _ and -
Using the following string works for all except the hyphen, which I assume is because it's an operator:
Is Null OR Not Like "*[!((a-z) or (0-9) or (_) or (-))]*"
Is there something I can do to make it recognise the - as text?
I've been writing queries in the following format for years in Access 2003, but having recently transistioned to Access 2010, I've found the following sql doesn't work.transform
sum(s.value)
SELECT
s.sn, s.ln, s.pn, s.id, s.lat, s.point, s.supply_type, s.used, s.real, s.code, c.name
FROM
supply_points s
, codes c
WHERE
s.code = c.code
and s.id is not null
and s.code = 1075
and s.month >=[forms]![main]![gppstart ]
group by
s.sn, s.ln, s.pn, s.id, s.lat, s.point, s.supply_type, s.used, s.real, s.code, c.name
pivot
s.month
In Access 2010, this query returns the following error message:the Microsoft Access database does not recognize '[forms]![main]![gppstart]' as a valid field name or expression
Is this a common phenomena in Access 2010?
I have a small database for producing various financial reports, by date period (from/to). It works perfectly except when there are either no payment records or no receipt records for the chosen period. Naturally enough, MS Access comes up with the message "Database engine does not recognize 'payment' as a valid field name or expression" --- !!!
Is there some way I can tell MS Access that I don't mind if eg the payment column result is zero?
The structure of the table on which the report is based (via a crosstab query) is :
transaction date
auto number ID
transaction type (either payment or receipt, chosen by form's drop down box) - TEXT
amount - CURRENCY
receipt type - TEXT
payment type - TEXT
fundno - TEXT
The crosstab query design is as per the attached jpeg file
Hi all, I am utterly unsure if what I want to do is even possible:
I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).
There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.
I want to make another crosstab query which performs a calculation. To keep it simple:
If (RewCOCredit>WrapCOCredit) Then
5-RewCOCredit
Else 'WrapCOCredit>RewCOCredit
5-WrapCOCredit
Please help!!!
I have a query where these are the fields:
ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC
The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)
When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?
Access 2007
Unbound Form
I have a onclick tied to a label (for decoration purposes) that when clicked it launches VBA that essentially updates a form. All that part works except it will not recognize any changed value of the field I was last in?
Just to try to explain best as I can what happens.
- Form gets opened
- I change field (quantity field)
- I click the Label
- It reverts to pre-existing value.
if I click off of the text field first then do the onclick - it recognizes just fine.
Any way to have a form with Dates as column headers to update a table where the dates are stored in rows???
The table set up is like this:
tblOpHdr
DiaryID (PK) - OpDate (Date)
tblOpDetail
DiaryID (FK) - CostCode - MachineNumber - MachineHours - etc
I'm just wondering if there's any way I can do this with a datasheet or a crosstab type setup?
It's Access 2010.
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?
View 1 Replies View RelatedI 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
02-Apr-2015
03-Apr-2015
04-Apr-2015
05-Apr-2015
06-Apr-2015
07-Apr-2015
08-Apr-2015
09-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?
Hiya-
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?
Many thanks, Jules.
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
table2
01/01/2014
02/01/2014
05/01/2014
17/01/2014
18/01/2014
20/01/2014
21/01/2014
25/01/2014
So the answer of the query would be 2,0,4.
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)
Is this possible in access?