I'm trying to query excel file which contains a column named "Date",
so when I'm trying to do a query like "select * from Table_Name" it's working fine, and I'm getting all the data including the column named Date, but when I'm doing any query which is referencing "Date" I'm getting an error "Syntax error in date in query expression"
This is due-I think- to the fact that Date is a reserved word...but when I'm trying to change the column name Date to anything else say "Date_Debut"
the problem is still there, so is there any special thing to do when querying Date column type?
Please help!
Thanks,
welid
Basically I want to execute every query within my database that exists in one of my tables. It should fill a recordset with the query names and loop through the database and execute each one.
When I try and run access keeps saying run-time error 3061 too few parameters (yellow line). I know the issues is with the sql, but not sure how to get around it or if this type of query is do-able.My code below:
Public Sub C38() Dim rst As DAO.Recordset Dim DB As Database Set DB = CurrentDb() Set rst = DB.OpenRecordset("mTable")
what is the correct syntax to string in a variable date?
here is my current coding
[CODE]
Set rsDate = CurrentDb.OpenRecordset("qry_Actual_Costs_Thru")
strSOPCDate = rsDate.Fields("Fiscal Week")
'need to string the file name with the date
ActiveWorkbook.SaveAs Filename:= _ "C:my folder locationCombined SOPC THIS IS WHERE I NEED THE strSOPCDate.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False
CODE]
my overall goal is to save a weekly file named "Combined SOPC m/dd/yy" where the m/dd/yy is being pull form strSOPC Date whick pulls from my qry.
Ive got a combo box linked to fields in a form so when I value (tenantID) i selected in the combo box the record appears in the form.
The problem is, I have changed the form name Unbound (it was blank) to TenantID as I want it to appear when the form is opened, however now I cannot select other items from the combo box.
I need to daily import files that start with same name, but the remaining part will be Bill of lading ID (e.g. TEST_BOL 1234.csv). The next day will be TEST_BOL 1235.csv) and so on. I need for Access to import the file based on the "TEST_BOL" into my database and then delete the file from the folder. I don't know how to initialize Access to look for the prefix and import in.
I have a excel object I have opened, and want to copy a query into a named range on the workbook.
heres the start have made
Sub openExcel() 'WillR - opens the specified Spreadsheet Dim xlApp As Excel.Application Dim xlWB As Excel.Workbook Set xlApp = New Excel.Application With xlApp
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006 and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
I am creating a repayment schedule (as a report) and I want to display a series of fields as a column which return (show) a date one month after the date in the field above.
The first repayment date field (Line 1) will show a date one month after the loan was paid out. the Next field below will show the date one month later.
I can't simply insert the "Date + 30" because that would get out of since over the year. If the loan was issued on say the 15th of January, I need the first field to display 15th February and the next would be .... 15th March.... Yes - You've got it!
Now I could do that in Excel, but I don't know how to get Access to do it.
I have a table having column as date, format as mm/dd/yyyy and i update the column as now() using VBA.However some places the date is in mm/dd/yyyy and in some mm/dd/yyyy nn:mm:ss are appearing which is making the filtering of the data ambuige.
i didn't think things through when i initially created the DB, and created a column called dateOfBirth that holds user entered dates as dd/mm/yyyy. This causes many query issues, and I would LOVE to have this changed.
Is there a script i can run that will take dateOfBirth, determine the month, write the month as Jan/Feb/Mar/... in a column named dobMonth, and take the day value from dateOfBirth (1-31) to a column called dobDay?
this is part of my table. It mean that empid-1 will work task 2 from 17/7/2013until 20/7/2013.Now i face problem in query that I have criteria that emp_id is 1 and date is 23/7/2013, I want to know which task_id assigned to emp 1 on 23/7/2013. The result should return 3 as the task_id
How do you merge two columns together, one is the date, and another is the time.
date column as this 9/21/13 time column as this 11:01:00 am
the reason why I want to merge it is because so I don't have to retype and I would have both the date and time in one filed. Date and time column are both Date/Time type.
I have a crosstab query that groups by week to obtain columns for monday to sunday. Is there a way to obtain the actual dates that these columns represent?
Sorry to open with a question but its usually the way, i'm quite an experienced computer user but never used any office products before and just thought i should learn as i'm losing money every week by not keep track of things properly.
So then to my question..
I've made a table to cover all the orders i have had placed, this includes a date column for when that order was placed. I just wondered if it would be possible to have some kind of system where after 7 days without any acknowledgement from me it either pops up or turns red or something similiar. I'm thinking the acknowledgement could be the yes/no box and i tick that when i recieve the item back, if i dont it either pops up or turns red or something :confused:
I have form with these info : customer (Table), EnterDate (Button) and customersub (subform), i want to enter date into Date column using form with button.
example
ID name sample date 1. John A .... 2. Michel B .... 3. Jack C .....
into like this
ID name sample date 1. John A 2/2/2014 2. Michel B 2/2/2014 3. Jack C 2/2/2014
I try this this sql. but the date only insert/update for avery row i was select.
Q: How i can make sure i put one date.. it will insert for every row just one click.
Private Sub EnterDate_Click() CurrentDb.Execute "UPDATE Customer " & _ "SET Date='" & Me.insertdate & "'" & _ "WHERE ID= " & Me.Costumersub.Form.Recordset.Fields("ID") End Sub
I have the below query (A). How do I calculate the Day of Therapy as shown in B
A MRN Last Name DOA Service startDate INR Dose WarfID VisitID 12001 Smith 6/28/2013 Warfarin 6/29/2013 1.20 6 mg 10 138 12001 Smith 6/28/2013 Warfarin 6/30/2013 1.20 6 mg 11 138 12001 Smith 6/28/2013 Warfarin 7/1/2013 1.3 6 mg 12 138 12001 Smith 6/28/2013 Warfarin 7/2/2013 1.40 6 mg 13 138 12001 Smith 6/28/2013 Warfarin 7/3/2013 1.40 7.5 mg 14 138 12001 Smith 6/28/2013 Warfarin 7/4/2013 1.70 7.5 mg 15 138 12001 Smith 6/28/2013 Warfarin 7/5/2013 2.00 7.5 mg 16 138 11002 Brown 7/1/2013 Warfarin 7/1/2013 1.00 2 mg 17 139 11002 Brown 7/1/2013 Warfarin 7/2/2013 1.3 4 mg 18 139 11002 Brown 7/1/2013 Warfarin 7/3/2013 1.50 2 mg 19 139 11002 Brown 7/1/2013 Warfarin 7/4/2013 1.8 2 mg 20 139 11002 Brown 7/1/2013 Warfarin 7/5/2013 2.20 2 mg 21 139 1205972 Jones 6/28/2013 Warfarin 7/6/2013 1.0 6.5 mg 29 138
[code]....
What I really want is to calculate the time (# day) it takes to reach a target goal INR (2-3) I have tried DateDiff between DOA (date of admission) and Start date but this obvious won’t work because some patient won’t start on warfarin until later.
Another possibility is to calculate date difference between two rows in same column but I don’t know how For example Pt 1 = It take 7 days to reach an INR of 2, Patient #2 = it takes 5 days to reach an INR 2.2 and Patient #3 takes 6 days to reach an INR of 2.1. Average = 6 days.
I'm quite new to Access.I've got an long text field with logging, and an field within an form with column history. Is there a way to turn of the date and time stamp and the column history field?
I have combo boxes that contain attorney names and phone numbers. The attorney name is what is filled into the field when a selection is made from the combo box into the form. The phone number is just there for the occasions that the person managing the data in the database needs to call that attorney. Just a quicker way instead of having to go to another form to find the phone number. I created a query to combine the first and last name to one field (FullName) and this is where the combo box gets it's info.
When I run the query, the phone number is shown formatted as I want it to ((###)###-####), but in the combo box in the form, the phone number has no format to it. What I need to do to get the phone number to show up formatted in the combo box list?
i have 2 tables.. 1 with bookings and 1 with a BonusPeriod range.
in the Bookings table there is a field called [ServiceDate]
in the bonus table i have a field called [Period] which is text i also have 2 dates, [StartDate] And [EndDate] i need to Dlookup the Bonus table to return the value of the period table based on where the [ServiceDate] falls.
i dont want to use VBA (i really need it to work within a query that inserts it's data into another table)
i've tried everything but had no joy
Expr1: DLookUp("[Period]","BonusPeriods","[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","[Staff_BookingsAndQuotes_Master].[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")
Expr1: DLookUp("[Period]","BonusPeriods","(FormatDateTime([ServiceDate]),"yyyy-mm-dd") Between (FormatDateTime([BonusPeriods]![StartDate]),"yyyy-mm-dd") and (FormatDateTime([BonusPeriods]![EndDate],"yyyy-mm-dd"))))
One of the Field Names in my table is titled ActionDeadlineDate, with Date/Time selected as the Data Type for the field. On one of my forms I have created a text box based on this field. Under Properties/Control Source for this text box I have inserted the following formula:
=[DateReceivedinADR]+40 Basically, this forces the text box to auto populate to a date 40 days beyond whatever date is entered in the DateReceivedinADR text box. On the form itself everything works flawlessly.
I have created a query that includes the ActionDeadlineDate field. However, when I run a report off this query it will not populate the date for this field. On the report this column is just blank, with no date indicated.
All other date fields I use on the form (at least those without an underlying date formula) appear as they should. Is there a way to make a date based on a formula appear on the report as well?
Hope I didn't make this too complicated. Maybe what I am asking for is not possible since a date formula is being utilized in the text box, rather than a simple date.
