Please Help Out A Silly Prat With A Simple Date Question
Sep 16, 2005
I have the following fields in a query: "Employee_number", "shiftname", and "shiftdate"
And I have the following code on the criteria of "shiftdate": >= dateadd("d",-7,Date()) so that only records from the past 7 days are displayed which is fine, however when I run the query it displays the following
Employee_numbershiftnameshiftdate
1A16/09/2005
2A16/09/2005
3B15/09/2005
4B15/09/2005
which is not quite what i want.....Is it possible to make it only show the same shiftname with the same shiftdate just the once instead of showing it several times for each employee?
View Replies
ADVERTISEMENT
May 1, 2008
ok guys i know this is really stupid and silly but i cant see for the life of me what i am doing wrong i have a query where i want to only show records between to dates (including first and last)
when i do both or less than one date nothing shows using the code below
SELECT Sum(HTRXTBL.HTRX_QTY_1) AS QTY1,
Sum(HTRXTBL.HTRX_VALUE) AS VALUE1,
DateDiff("d",[Forms]![frmCOMPARISON]![txtDATEFROM1],[HTRXTBL]![HTRX_TRX_DATE]) AS [DAY]
FROM ITEMTBL INNER JOIN HTRXTBL ON ITEMTBL.ITEM_NUMBER = HTRXTBL.HTRX_ITEM_NUMBER
WHERE (((HTRXTBL.HTRX_REC_TYPE)="ITMSALE")
AND ((HTRXTBL.HTRX_TRX_DATE)>=[Forms]![frmCOMPARISON]![txtDATEFROM1]
And (HTRXTBL.HTRX_TRX_DATE)<=[Forms]![frmCOMPARISON]![txtDATETO1]))
GROUP BY DateDiff("d",[Forms]![frmCOMPARISON]![txtDATEFROM1],[HTRXTBL]![HTRX_TRX_DATE]);
when i do greater than the from date it this code below works
SELECT Sum(HTRXTBL.HTRX_QTY_1) AS QTY,
Sum(HTRXTBL.HTRX_VALUE) AS [VALUE],
DateDiff("d",[Forms]![frmCOMPARISON]![txtDATEFROM1],[HTRXTBL]![HTRX_TRX_DATE]) AS [DAY]
FROM ITEMTBL
INNER JOIN HTRXTBL ON ITEMTBL.ITEM_NUMBER = HTRXTBL.HTRX_ITEM_NUMBER
WHERE (((HTRXTBL.HTRX_TRX_DATE)>=[Forms]![frmCOMPARISON]![txtDATEFROM1])
AND ((HTRXTBL.HTRX_REC_TYPE)="ITMSALE"))
GROUP BY DateDiff("d",[Forms]![frmCOMPARISON]![txtDATEFROM1],[HTRXTBL]![HTRX_TRX_DATE]);
:( i cant see what is different or missing help please
View 4 Replies
View Related
May 24, 2005
Erm, can someone tell me how to add a new record to a table pls?
I cant find how to do it in the heklp section
Many Thanks
Andy
View 8 Replies
View Related
Sep 26, 2005
i've got a field in a form where u need to pick gender(in dutch)
Now there's an Attention further on and i need the proper attention:(in english= Male=Sir,Female=Madame) Which is in dutch: Male= M = De heer
Female = V = Mevrouw, Unknown = O or B = De heer/mevrouw, So my code became:
=IIf([Geslacht];"M";"De heer";(IIf([Geslacht];"V";"Mevrouw";"De heer/mevrouw")))
Can some1 please tell me what i did wrong?
Kind Regards
SilverBlood
View 1 Replies
View Related
Mar 21, 2006
hey guys
I want to creat something like
1. list of all countries, with one Bogus "All" representing all countries
2. When i select a Country from drop down list, i should get list of States in that Country. Again with one Bogus "All" representing all States in that Country
3. When i select a State from drop down list, i should get list of Cities in that State. Again with one Bogus "All" representing all Cities in that State
Can anyone suggest to find solution
Thank you
View 1 Replies
View Related
Nov 23, 2006
Hi fellas,
thanks for all ur help in my previous posts.......
ive got this problem, ive got a form that provides mobile details.
on the same form theres an add button and clear button.
all the validations work....... if the forms incomplete then validations do occur.
but the problem is......... if i clear the form (clickin on the clear button) and click 'x' to exit, it brings up one validation, which is the date_issued field validation????? and then exits
why does this validation occur??? how do i get rid of it.............
i want it to exit without any problem....... becoz i have cleared the form
View 14 Replies
View Related
Oct 25, 2007
Hi there,
I have been using Excel for many years and have just got to the stage where I need to build a database. I have managed to work out how to import the data ok but within the single table that I will be using I need to have a column which will add up the contents of four other columns.
EG.
Cells in Column T to be the total of cells in the same row from columns C, E, F and G.
Please would someone be kind enough to let me know if this can be done and if so how I can go about achieving this.
I am a newbie so please be gentle with me!!
Many thanks.
Paul
View 4 Replies
View Related
Nov 10, 2006
I have read as much as I can about using access and have decided it's time to have a play - so please forgive me if this is a silly question. Here goes.
I have a single table - it lists staff by name and staff number and shows 3 qualifications that they may or may not need (yes/no columns).
I have managed to produce queries/reports that show, for each of the 3 qualifications whcih personnel that require that qualification have not achieved it.
I now want to produce a query/report that lists any personnel that are outstanding any of their required qualifications and if so which qualification they are outstanding.
Any hints or tips would be very much appreciated.
View 3 Replies
View Related
Mar 6, 2008
I'm probably doing something daft but can anyone suggest why this query is giving no results?
The Item_Type field in the TBL_All_Items is a drop down menu
The query Q_Printers is supposed to be giving the results of only those item types beginning PRN but its showing no results.
View 5 Replies
View Related
Jun 6, 2007
I feel like a goober for even asking this, since I've been using Access for three years now for work-related databases, but this is actually the first time this has come up and I can't, for the life of me, figure it out.
I need to find out if there is a way to copy something (a year, for example) and paste that one thing onto multiple rows within one column of the datasheet (Year Attended, for example.)
I don't want to have to type in the year 1,296 times; I'd like to see if I can highlight the portions of that column I want (all of the people who attended in 2005) and paste "2005" into all of the highlighted cells at once. In Excel, for example, you can copy something, highlight an entire section, and it will paste into each cell.
Any help would be appreciated!
View 2 Replies
View Related
Dec 15, 2005
Hey all,
I have this code on a number of difference forms:
[Forms]![Care Packs].Customer.SetFocus
This is executed by an event in another form that loads the new form and starts dumping in values. For some reason I'm suddenly getting "Object doesn't support this property or method. 'Object' is simply a textbox that is enabled, but is also locked altho to my knowledge this isn't a problem. It's late and I've been working for hours so this may be something simple I've overlooked...need to step away for a while.
Any thoughts?
View 4 Replies
View Related
Nov 20, 2007
hello all,
i would be extreemly grateful if anyone could offer me some help with this,, basically i have two tables containing the same fields,, one of the table has approx 3k entries where the other has approx 100 entries,, table two (the smaller table) has the same data the only difference being the percentage filed will have increased. for example Tabel 1 has JC111 at 10% and Table 2 has JC111 at 20%,, my life would be made so very much happier if i could find a method of running an up-date query that would take the 'new' % values from table 2 and post them into or create a new table that has the correct/new %'s.
i really hope this makes some sense,, i would appreciate anyones help
best regards
View 2 Replies
View Related
Oct 3, 2006
Hi all,
Please forgive a newbie that asking the stupid question.... i just wonder is that anyway to set the date format to short date with instead of mm/dd/yyyy to dd/mm/yyyy to let the user to keyin?
Thanks alot.
View 3 Replies
View Related
Jul 5, 2006
I got this to work easily on another form but on this particular form it doesnt work. I enter a command button and name it d1, then I enter this code for it to populate the current date in the field next to the box:
Private Sub d1_Click()
Date_Entered.Value = Date
End Sub
Can anyone see why it doesnt work :eek:
ERROR IS:
Run-time error '2465':
Database cant find the field 'Date' referred to in your expression.
When I enter a date field it enders whats in that field (blank) into the field I told it (Date_Entered) on my form.
View 3 Replies
View Related
Jul 5, 2006
I got this to work easily on another form but on this particular form it doesnt work. I enter a command button and name it d1, then I enter this code for it to populate the current date in the field next to the box:
Private Sub d1_Click()
Date_Entered.Value = Date
End Sub
Can anyone see why it doesnt work :eek:
ERROR IS:
Run-time error '2465':
Database cant find the field 'Date' referred to in your expression.
When I enter a date field it enders whats in that field (blank) into the field I told it (Date_Entered) on my form.
View 2 Replies
View Related
Sep 13, 2006
OK... what am I forgetting?
I have a linked SQL table with a Date/Time field called DATE. ALL I WANT TO DO is pluck all the records with a single date from that table.
Predictibly, the date field has values like this:
9/11/2006 12:40:46 PM
If in the MS-Access query I use criteria of #9/11/2006# I get other dates also. I have tried variations on Date() or DatePart but those either fail or don't work. Sheesh.
ULTIMATELY, I want to restrict records to a specific date, and then a certain hour of that date, so if you can include this bit of additional logic in your response, that'd be great too. Note: I am trying to structure a query, not write code.
Thanks!
View 2 Replies
View Related
Dec 29, 2006
Ok I am right now making a simple Vendor/Product database to create a line sheet for some sales folks. I have 3 tables: Vendors, Products, and an associate entity Vendors_Products to relate the two. I have a form currently that draws the Vendor Name (primary key) from the Vendor table and the Product Name from the associate entity. This allows me to create new vendors and select current product types from a drop down box. The problem is that the drop down box is too long and it is tiresome when 1 vendor has 10 product types.
Can anyone tell me how to resolve this? I thought it would be better to have option buttons and display all available products. Then you could just click all of the option buttons that apply to that Vendor and it would create the relationships...is this possible?
View 1 Replies
View Related
Aug 19, 2015
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
View 13 Replies
View Related
Oct 24, 2013
I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"
I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =
Code : DateAdd(d,2,[IncidentDate])
so I removed the parenthesis and nothing happened
Code : DateAdd d,2,[IncidentDate]
I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either
Code : DateAdd d,2,[ContainDueDate]
I'm not sure if I'm even using the right function to get what I want.
View 4 Replies
View Related
Oct 9, 2005
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.
View 9 Replies
View Related
Nov 22, 2005
Hi,
I have looked at some of the threads here and it is clear that many of you are working on a much higher level than me and with a high degree of familiarity with the programme.
I am hoping that someone here is able to give me some advice as I don't find the MS help files digestible.
The task I have is to join 2 databases and produce a table from which I can run a mailmerge.
I have managed to join the 2 databases and I used a customer ID as a common link. (my apologies if the terminology is incorrect)
I now have all the data I require in one table.
THE PROBLEMs I have multiple entries for some of my customers and would like to reduce this to single entries (which is understandable). Please tell me how to do this if you can, and keep it as simple as you can please.
In anticipation I thank you very much.
:-)
View 2 Replies
View Related
Jan 14, 2006
Hi there,
I'm making a report detailing competitors final finishing positions in a competition.
The columns are (1) Place, (2) Name and (3) Points
I have the data for their names and the points they have scored, but how do I make the table enter the record number (starting at 1) in the first field???
Thanks in advance
Col
EXAMPLE
PLACE NAME POINTS
1 John Smith 199
2 Ian Jones 90
View 1 Replies
View Related
Feb 16, 2006
Not sure what the issue is here but i am trying to append a Job number from a main database into two connected databases.
The number is a sequential number i have created and the primary key.
It is an autonumber field(obviously). The format is J0000 and the numbers start from J0001
I am using two different append queries to append them into the external databases tables.
However (and here i think lies the problem) the fields i am inserting them into are text fields with the same format J0000.
When i view the numbers in these tables, they are displayed as J1 or J9. The 0's are missing.
Any ideas.
Thank you in advance
View 10 Replies
View Related
Feb 21, 2006
Hey guys, I'm just wondering how I could limit a field to using from 2 - 20 letters. If I placed the letter "a", I'd get an error saying how at least 2 letters must be there, and a maximum of 20. How would I do this?
Lastly, how can I make it so when I enter data into a form, I can click a button which adds it all into the table?
When I do it normally, it's automatically transferred into the table as I type it into the form.
This is incredibly simple I know, but I am a n00b at Access. ^_^
Thanks guys.
View 2 Replies
View Related
May 18, 2005
OK, I have a strange problem... This doesn't seem to work..... It did... then I made some changes.... Can someone clue me in on what I am doing wrong? A query, simply summing some values in fields... no biggy...... BUT...
The field
TotalHours: ([SundayHours]+[MondayHours]+[TuesdayHours]+[WednesdayHours]+[ThursdayHours]+[FridayHours]+[SaturdayHours])
So why isn't this working?
Can I use a simple VBA statement to sum these fields?
:confused:
View 14 Replies
View Related
Nov 24, 2005
hi all, this is my problem:
i got a table like this
ID time in time out working hours
1
1
2
2
the working hours is [timeput]-[timein]
how to i create a query so that all the ID 1' s working hours will be summed up ?
View 4 Replies
View Related
