Extract Date
Aug 22, 2006Hi,
If I have a date that is formatted like this 01/01/2006, however do I write a query that shows the date as follows Jan 2006.
Thanks
Regards
ADVERTISEMENT
Extract Date From Filename
Apr 12, 2008I think I am on the right track, but I am stuck with importing the date from my excel filename. Currently the following code imports all Excel files from the directory into a table in my database. The excel files are saved like "ABC_BNG_GTR_04012008.XLS" The numbers represent the date. I need for the date to be extracted and place in table into the "callDate" field. When I run my code, I continue to get a runtime error 13 - type mismatch. The code stops at the Mid() statement. What am I doing incorrectly? Thanks in advance.
Option Compare Database
Private Sub btnImport_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim filename As String
Dim path As String
Dim TheDate As Date
DoCmd.SetWarnings False
path = "C:UsersChinaboyDesktopData"
'Loop through the folder & build file list
strFile = Dir(path & "*.xls")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files
For intFile = 1 To UBound(strFileList)
filename = path & strFileList(intFile)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "sheet2", filename, False
'Inserts date in date column based on file name
TheDate = Mid(strFile, 12, 8)
CurrentDb.Execute "UPDATE sheet2 SET callDate =" & "'" & TheDate & "' where callDate is null"
Next intFile
DoCmd.SetWarnings True
End Sub
Need To Extract Month From A Date
Apr 22, 2008Hi - I need to extract the month from a date via an SQL pass through query. e.g. if the date reads 19/02/2008, I need to have returned to me 'FEB', so that I can link the month (string) to another table.
I have though about "decoding" the date - I've seen that practice used in other scenarios, but never on a date format. Any suggestions?
Thanks
Extract Date From Timestamp
Apr 23, 2008I have a timestamp field in a database and only want to display the Date. In the query I placed this: Date Received: Format([RItemTimeStamp],"Short Date")
Works fine however if I try to run a query to serach for a date range it doesn't give me all the days. Is there a better way how to truncate the Date of a timestamp so that I can sort it correctly?
How To Extract Month & Year Together From A Date
Apr 19, 2007Hey guys.... stucked in one thing..... I need to extract Month and Year together from a date in a Query. for example if date is 4/19/2007 i want to put this as April 2007.
Any idea ...?
thanks
How To Extract Date From Personal ID Number
Oct 4, 2006Hello,
I would appreciate if someone could help on this problem.
What I want to do is to add a field in a form that would automatically show the date of person's birth after entering the persons ID. For example, I enter 39101012345. The first number (3) shows only the gender so I don't need it. The next 6 numbers show the date of persons birth - it is 910101 and it means that the birthdate is 1991.01.01
I need this field in a date format because later I will have to do some calculations concerned with the birthdate of that person.
Thanks in advance!
p.s. beer for help is quaranteed! :)
Queries :: Extract Max Date And Time - How To Get Corresponding ID
Jan 2, 2014I have a (simplified) table with
ID | Date | Time | machine | value
304 1-1-2014 06:00 115 0.54%
305 1-1-2014 06:00 111 0.56%
306 1-1-2014 07:00 111 0.52%
307 1-1-2014 07:00 115 0.53%
308 2-1-2014 07:00 111 0.56%
309 2-1-2014 07:00 115 0.58%
310 2-1-2014 06:00 111 0.54%
311 2-1-2014 08:00 115 0.53%
I try to find the ID corresponding to the maximum date+time grouped by Machine.
In this case I would like to find 311 (maximum date/time for machine 115) and 308 (maximum date/time for machine 111)
The way to extract the maximum date + time is not difficult
SELECT Max([Date]+[time]) AS datevalue, tbl_TexControl.Machine
FROM tbl_TexControl
GROUP BY tbl_TexControl.Machine;
But how do I get the corresponding ID? Not with Max([ID]) because then ID 310 is selected which is not the maximum of the time.
Queries :: Extract Day From The Date And Return A Value
Feb 20, 2014I have a list of dates on which tasks were performed.
I want to be able to see if there is a pattern, i.e. lots done on a Monday
Can I extract the Day from the date, and return a value Monday, Tuesday, Wednesday etc??
Help - Need To Extract Year From A Short Date Field
Oct 13, 2006Hi folks - I saw a thread last week re how to extract the year from the date field possibly using date serial but can't find it can anyone help - it is probably very simple but i can't get it to work.
Ta
Humph
Help - Need To Extract Year From A Short Date Field
Oct 13, 2006Hi folks - I saw a thread last week re how to extract the year from the date field possibly using date serial but can't find it can anyone help - it is probably very simple but i can't get it to work.
Ta
Humph
Queries :: Extract Day Of Week From A Date To Link To Data
Dec 8, 2014I want to use the expression Date() in a query to get today's date and then format this to "ddd" to give me the day of the week.
I then want to link this "ddd" to data in my table that contains the three letter days of the week.
How do I make the day become "static" in order to be able to link.
Calculated Field To Extract 14 Day Period And Check If Today Date Falls Within?
Dec 3, 2012I've got a members table where my members pay an annual fee. The fee is paid 12 months on from when they originally registered. So, for example, 12 months from today, i would expect to pay my next annual fee.
Now, with different members registering at different times of the year, it isn't so straightforward. I would like my calculated field to indicate to me if a payment is required.Furthermore, I would like to include a '' date range '' so that the calculated field entitled ''Payment Required?'' shows yes for a fortnight. Here is my attempt at the expression which partially works:
PaymentRequired?: IIf((Format(Date(),"dd/mm")>= [RenewalDate] And Format(Date(),"dd/mm")<=[RenewalDateAdd14])Or ([Subscription Paid?]=0),"Yes","No")
i used the Format Date method. But i've got a case where someone is due to pay at the end of November...and today being teh 3rd of December.they should have a good few days yet to pay the fee (before teh aforementioned 14 days is up)
Modules & VBA :: Unable To Extract Day Number From Date Field And Write It To Day Field
Jan 10, 2014I am trying to do some simple table operations. I have a field (Date) containing dates, and an empty field called Day.
I want to extract the day number from the Date field, and write it to the Day field.
I didn't get very far until I ran into trouble when setting my recordset. I get the error "Too few parameters, expected 1". Clicking "Debug", will highlight the code line "Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)".
So far, my code looks as follows:
Code:
Private Sub Command16_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlString As String
Dim dataDay As Byte
'Open connection to current Access database
Set db = CurrentDb()
[Code]...
I am not very familiar with the various types of recordset settings. I just want to be able to read data from the Date field, and write data to the Day field.
Extract Data
Dec 6, 2005Currently I have a remark field and want to just extract the amount. The data input is not in a structured manner therefore I could not use the mid function.
Example :
Remark
CASH PURCHASE $50K.DEBIT 3007484701
SUB $20,000 FIRST STATE DIVIDEND
AMT:$10,900(FS BRIDGE FD)NO DISC
Extract Text
Sep 13, 2006Hi,
I am currently working on a report that i need to extract part of the data from the memoranda field. i have a problem to extract the details that i need.
Example as follow :
Field Name = Memo
Details in Field = ?********~SPGEO1398789159173897 *********?
what i have to do is to extract the data starting from "SPGEO1398789159173897". Also the length of the number can be varied.
i have tried to use the instr, Trim, LTrim but still couldn't get what i wanted.
Can someone here please give me a hand on this
Much appreciate for your help
Ronny
How To Extract Only The First Four Characters
Mar 20, 2007Hi, Everyone,
I have an excel column that has building location and building name in one column, example 1245 Accounting, how can I copy the 1245 into it's own column, please help, i can do it either in access or excel. Please.
Extract Substring
Mar 31, 2006I have a table in which one field badly needs separating into two. Currently, it is laid out as a mixed case text string followed by an upper case text string, e.g. O little town of Bethlehem FOREST GREEN.
I need to move the upper case string (FOREST GREEN) to a new field (and then trim the original field accordingly). Since there is no delimiter between the two parts of the field, I have no idea how to proceed. Can anyone help?
Thanks.
Extract Certain Data
Aug 15, 2006I have a very simple question that I have not find the result or maybe I do not know what to look for!
I have a query containing a field named year and one named sales, what I need is to have a single result of total sales per year. example
year sales
2005 100
2005 100
2006 80
2006 70
2006 60
The result I would like to put then in a form is:
total 2005 200
total 2006 210
All this by simply running a query without indicating any parameters.
Thanks
M
Extract Data
Aug 17, 2006OK, so i have finally got round to re-building my client's database as suggested several times by RV.
I have three main tables that I need to extract data from - tblMembers, tblModules, tblCompleteModules.
Basically its a database containing the training details of Scout Association leaders. Each Leader needs to complete 26 modules over a period of time.
tblCompleteModules contains the data for the modules that have been completed, MemberID, ModuleID Date Completed and CompletedID.
I know how I can extraxt the modules that members have completed, but how can i extract the data for the modules that have not been completed. For example I need to report on the number of people who haven't completed module number 20 for example.
Any ideas?
Want To Extract Record
Nov 3, 2006i have some problem regarding this.--
I hve two table- 1.Ordbook
2.Ownmas
in Table Ordbook there is two field ORD_NO and Location
in Table Ownmas There is also Two field Ord_no and Sch_val;
but i enter the order no in table Ordbook normally.
suppose 7825,5855
but i enter the order no in table Ownmas as 7825/5855
so when im going to join these two tables by linking ORD_No and Ord_no. i find there is no match. bt my requirement is to match these fields by ANY no and want to retrieve the sch_val from Ownmas .
How To Extract Value Within A Parenthesis?
Apr 30, 2008Hi,
I have a table which gets data from an external text file. I imported this into an Access table (version 2003).
I have a field (PersonName) in the table (Employee_Credits) which has data in the following format.
FirstName LastName (EmployeeID)
It is always in the same format. Using an SQL Query in MS Access, how can I extract the contents within the parenthesis and insert into a column titled “EmployeeID”
Any ideas?
Thanks.
Extract Data From Word Doc
Mar 11, 2008I have a word doc from which I want to import data in a Database.
The word doc has a table, something like this :
Projectnumber 10700004
LSname LSTK number one
POnumber 1170-LST-04
Contactname Mr. Karel van Straten
Vendorname Mothercompany name S.A.
Manufacturer Operating Company name
Otherdata Others
Mancity 46100 Hamburg
Mancountry Germany
Manorder 31-0111-009
Mancontact Mrs. Angela Duval
Mantel 0049 2323 209 151
Manmail email@testing.de
At this stage I copy the table data and paste it into a memo field in a Dbase Query and try to manipulate the text in that query to get some of the data segregated , i.e Projectnumber : 10700004 , Mancountry : Germany , etc.
However, this doesn’t work as I want.
e.g. I’m trying to get the Manmail text thru this qry :
Manmail: Mid([Memofield],Len([Memofield])-InStr([Memofield]," ")) , but the output is not consistent.
Would there be a better way to get the original ( doc-table) data in the same way in my database.
Note :
I could use an additional step : copy/paste to Excel and then import to Access, but that’s something I would like to avoid.
Any suggestion will be appreciated.
Cheers, Ron
Extract Data From One Field And Put In Another
Oct 22, 2005I am trying to extract data from one field and then put it into another field. I have 2 fields, Code_A and Code_B. I am trying to take the Code * data from Code_A and put it in Code_B. Any help is appreciated.
Code_ACode_B
AlphaCode 3
Code 3
AlphaCode 3
Code 2
DeltaCode 3
Code 3Unknown
CharlieCode 3
Code 3
Extract From 1 Field And Populate A Whole Lot Of Others..
Sep 19, 2006I have read some tutorials online on how to split data into multiple fields.
http://www.databasedev.co.uk/splitting_fields.html
While this is the gist of what I need - it is much more complex.
I am dealing with adresses.
This inherited database has 2 fields - ZIP, and ADDRESS. I need to extract the data within the ADDRESS field to populate other more detailed named fields.
I would like to ultimately have my fileds to be: STREETNUM, STPREDIR, STREETNAME, STREETSUFX, SUD, SNUM.
Example: (Current ADDRESS data)
512 N Manchester Dr Ste 201
(extract ADDRESS and import into)
STREETNUM = 512
STPREDIR = N
STREETNAME = Manchester
STREETSUFX = Dr
SUD = Ste
SNUM = 201
--
Now I have tried several queries to extract the first bit of data 'STREETNUM', using the example on the link I posted above. INSTR, RIGHT, MID, LEFT.
Problem is there are also P.O. Boxes formatted like: PO BOX 1234, so my STREETNUM column includes the PO, when it should include the 1234.
PO Box 1234 should translate to:
STREETNUM = 1234
STREETNAME = PO BOX
All the rest of the fields aren't needed.
As you can see, I'm in OVER my head.
I would greatly appreciate any assistance or direction in accomplishing this task.
I am not a beginner with Access, but I am far from being an expert. A breakdown of some complicated tasks may be necessary... :-)
Thanks in advanced.
Extract Text Between Parentheses
Sep 12, 2007I have an expression below that I am trying to use to extract text from within parentheses in a field, but it is leaving the last ")" on the extracted text. Can anyone help me determine what I need to do to just return the text? Thanks.
The length of the field is 100.
Mid([SIDE_DRUG_DESC],InStr(1,[SIDE_DRUG_DESC],"(",1)+1,Len([SIDE_DRUG_DESC])-(InStr(1,[SIDE_DRUG_DESC],"(",1)+1))
Extract Unique Values
Feb 17, 2005I have the following SQL definition:
SELECT DISTINCT tblFile_Management_Received.Claim_Number, tblFile_Management_Received.Date_Checked_In, tblFile_Management_Received.Checked_In_By, tblFile_Management_Received.Copy_or_Original
FROM tblFile_Management_Received;
The results would show as:
ClaimNumber Date Checked In Checked In By Original or Copy
111111111 2/16/2005 8:34:24 AM XXXX Yes
111111111 2/16/2005 1:23:19 PM XXXX Yes
222222222 2/16/2005 8:34:52 AM XXXX Yes
222222222 2/16/2005 1:24:49 PM XXXX Yes
I am trying to use "Distinct" to show the following records when the query is ran (the desired results would be each unique claim number based on the earliest check in date):
ClaimNumber Date Checked In Checked In By Original or Copy
111111111 2/16/2005 8:34:24 AM XXXX Yes
222222222 2/16/2005 8:34:52 AM XXXX Yes
Can anyone help me with this??
Any help is greatly appreciated.
Thanks