I 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.
I have a link table that has a field that represents dates - but they are actually just text. It's a long story but the source is not going to change - so I have to try and deal with it. I need it to behave as a date - and am hoping to do this in a query. The data looks like this:
7/24/12
10/08/13
I have tried various things but it does not seem to totally do the trick - if I sort on it it still does not sort as one would expect from an actual date field. How do I do this?
I 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.
I 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??
Full Name, Forename, Surname, Salutation. This data has been extracted from another database where the Surname field was not required but it is now. So i need to find a way to pull data from the salutation field into the surname field where salutation has data but surname is null. The next one when surname and salutation are null but full name has data i need to pull that across.
View 11 Replies View RelatedI am trying to extract data from a query. Part of code when form loads, my questions is bold:
'query criteria
vOrgCode = Me.tOrgCode
'sql statement
strSQL = "Select tblMembers.tOrgCode, tblMembers.tGender, tblMembers.nMonthlyIncome, "
strSQL = strSQL & "tblMembers.nFarmSize, tblMembers.tPrimeCrop, tblMembers.nNoHills "
strSQL = strSQL & "From tblMembers Where tOrgCode = '" & vOrgCode & "'"
[code]...
i cannot build a simple query and save it as reference since the number of organizations may be 25 at the minimum. this is why i want to build it on the fly, so to speak.
I've been trying to create a query that will take a date and return the week number of the date.
My original date is formatted m/d/yyyy
I need my weeks to start on Monday and I would like the week containing Jan 1st to be the first week of the year
I have tried using the following function:
DatePart("ww",#12/31/2001#,2,1)
but the I get a result of 53 in this query, when I expected/need it to be 1.
I am trying to pull between the current date minus a week in my Access query and I do not know what formula to use.
View 8 Replies View Related102,34112,021502503130010010000,QAR,1,24,1,{NULL:0 },{5:Y},6,14.5,31-MAR-2011
Above data is in one field , i want and expression to extract the data between two consecutive comma set by specifying the comma range.
For example if i specify range 2 to 3 then formula should return "021502503130010010000"
Is there a way I can take each entry in a memo field and put it into a text field in a separate table. The database is getting really big and the customer notes field for each record has lots of entries. The memo field looks like this:
8.4.14 Ordered 2 cartons
20.3.14 Ordered 2 cartons
4.3.14 Ordered 2 cartons
18.2.14 ordered 1 carton
30.1.14 ordered 3 cartons SCENTED wipes
[Code]...
I want to take each line and put put the date in a date field and the text in a text field in a separate table linked by CustID. Is there a way to do that?
I have a table with a field named BinNo which has a list of items with a bin no in the format 1.234( this number can be any number up to 6000.9999).
Is there a way for me to have a field on a form where I enter a number (i.e 1) and it lists all the items with a 1 before the decimal point but not 10 or an other number with a one in it. If I enter 10 then it will only give me the items with 10 before the decimal point and not 11 etc.
I have a query that pulls all the data into it but I only want the query to show the items relating to the number I enter into the form field.
I have a weekly list of transactions that come in the format DD/MM/YYYY HH:MM:SS, I need to tag these individual transactions with a week number. The problem is, I can't use the Datepart function etc. as the day is classed as running from 8am to 8am rather than midnight. I have a list of all of the weekly date ranges for a few years (with the time) so I was able to solve this problem in excel by using the Index and Match functions. However, I'm trying to automate this process as much as possible so I'd rather perform this function in Access.
View 4 Replies View RelatedI am trying to create a query that will group my data by each week so I can sum up some numbers and display them on a per-week basis... Prefer the week to start on Sunday but really not picky about it.
Using the following in the query design window.
Week of Year: Format([Date],"ww")
This is returning:
1
10
11
12
2
3
4
and so on.. See the problem?
I tried sorting but that doesn't work.
I also tried the following:
Format([Date],"mmmm,ww")
But this is returning
December,51
December,52
February,8
February,9
January,1
January,2
January,3
Yes, there are some weeks without data but that's not the problem. The issue is that its putting February before January.. Why is this? Again, I tried sorting options and several other techniques but no success.
I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record
EG
ID1,10,write a report,2400
I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:
ID2,5,write a report,1200
ID3,5,write a report,1200
My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.
using Access 2010, I have reached the 2GB limit. It's not the data, it's the PDF files I scanned in for each customer that has stretched the db to its limits. The db is also currently split.
I didn't realize Office had a 2GB limit; I assumed if you had a 64 bit computer and a NTFS partition, there would be no 2GB limit.
Using a template database in Access 2010, I need to extract the attachments to a folder and thus link them back to the database using hyperlinks. I can handle this task, but I'm not confident how to extract the attachments from each record (around 900) to a folder and have it do it automatically for all records. I can do it record by record, but that is not feasible with this many records.
how to extract all the attachments for each record, and have them saved in folders by their name or other ID, so I can link them back to the db via a hyperlink?
I have to make a query using the following case, I have two tables, in the first table i have the materials and its received date and in my second table i have the month the target date for every month to receive the material. Now linking this two tables, i want to see the from the first table on every month hom many materails i received after the target date how to do this.
Table 1
Field1 Date
Field2 Month
Field3 Material
Field4 received_date
Table 2
Field1 Month
Field2 Target_date
I've been requested to make a bird count database.
The bird count data has been recorded on monthly papers like this:
[bird name] [week 1][week 2][week 3][week4]
The number of birds sighted for a given week is written in the [week x] columns.
What I need to do is make another column that shows whichever number is highest from columns [week 1], [week 2], [week 3], [week 4].
So for example:
Blackbird: Week1: 4, Week2: 2, Week3: 6, Week4: 2
highest: =6
Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.
I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.
I have been tasked with importing data from a clocking on system to analyse worked hours.
There is no option to amend the format of the export which is not great
surnameforenameField2Field3xxx
xxx
11/06/201319.18
xxx
xxx
11/06/20135.37+
xxx
xxx
12/06/20135.41+
xxx
xxx
12/06/201319.21
The plus indicates a night shift worker
eg started at 19.18 and finished at 5.37 on the 12th
so for every record I have staff id , date, and clock time ( I have stripped out the + ) and created a yes/no field to identify the records where field2 should actually be field2+1
I have sat in query design screen for ages and cant think how on earth I am going to calculate hours worked for a given staff member and date combination.
for every combination of staff id and date there should be 2 records - a clock in and a clock out
I thought about creating new fields clockin and clockout but struggling to see how I can link the 2 "paired" records together
vb script with dlookup for every record ?
Hi,
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
I 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
Hi - 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
I 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?
Hey 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
Hello,
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! :)
Hi 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