Prob. With Dates
Dec 7, 2005:( I changed my input mask in my form to 99/99/0000;0;_, but my form dates still look like 1/1 /2005. I want the 0's to be visible. Please help.
View Replies:( I changed my input mask in my form to 99/99/0000;0;_, but my form dates still look like 1/1 /2005. I want the 0's to be visible. Please help.
View Repliesin 1 table there are are 3 feilds
folwer_name
no_of_flowers
total_cost
another table (2 fields)
name
price
eg:
name price
rose 15
tulip 20
Now I want o make a form
with 3 fields
folwer_name
no_of_flowers
total_cost
eg: if some one select 'flower_name' 'rose' and quantitly '10' then total_cost = 150
How can I do this ? using query or anything else plz gimme detals
Hi, i have created two tables, containing the same fields. One table if for '1 day tests' and the other is for '2 day tests'. I need to keep these two in seperate tables.
At the moment i have two seperate forms, one for each type of test, but as the fields are all the same, i was wondering if i had a combo box, so the user could choose whether they wanted a one day or two day test and filled in the rest of the fields, the record would then be stored in the appropriate table. Ie 2 day test table, if the chose 2 day test on the combo box
Thanx
Hi all,
I have two date and time fields which format are like: 15/09/05 09:48:43,I have another field which calculates the time difference between these two fields. I am doing a calculation on the time difference field and want the time to be displayed as for example 03:00, but at the moment it just shows 3:00.
This is the control source which has some formatting in it:
=Int(CSng((([txtDateDel]-[txtdateRO])-[closed])*24)) & ":" & Format(([txtDateDel]-[txtdateRO]),"nn")
I have tried adjusting this but am having no luck.
I would be grateful of any help,
Thanks :)
How do I use a wildcard in a query, see attached .mdb
Table "myDATA"
Sam and Zeb have serial number 23a4224A and 23a4224 in their records.
I want to locate a serial number(s) in multiple fields of a table, based on the users input with a wildcard.
query "Serial Number query"
Enter Serial Number: the user enters: 23a4224*
What am I doing wrong?
Thanks
My combo box problem is still at large!
I've tried every way i can, and i cant get the rowsource query right.
If someone could look at this and post a fix, so that when one uses the "training record" form it allows you only to select course dates that are already in the tbl_course_dates table.
Thanks so much
http://www.suicidenotesmusic.net/nonworkingcombos.mdb
I'm trying to create a little dbase for personal keeping track of a number of companies I deal with, so that i store their details and also the products they distribute.
In more detail, I want to have 'fixed' product categories, which will be assigned to supplier, and each category will include the product models I have set, including their details (such as price, manufacturer, country of origin, etc).
My point is to be able to create several customised reports and listing:
1. All product models I have in a category including their details.
2. Report per supplier to view their details and the products they distribute
3. Certain product models in a list with their local distributor (companies I deal with).
I assume it involves a couple of many2many relationships, however I have created a file in MS Access but never managed to have it work properly.
Any1 have an idea, or template maybe?
Regards ;)
I want to delete a relationship temporarily so that I can delete a record. After the record is deleted I will restore the relationship immediately. But I am concerned that this will ruin any queries I have already established or ruin my other records.
Could you let me know if this is possible without making a mess of things that have already been established.
Thanks
Carrie
I have pasted the code that SJ posted on another thread that any changes to a record will ask the user to save before closing the form, this located in the beforeupdate event.
Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save changes?", vbQuestion + vbYesNo) = vbNo Then Cancel = True
End Sub
The problem i am getting is that if the user selects no it pops up another message saying "You can't save the record at this time" followed by a description.
How do i prevent this second message appearing i cannot find a thread with this in?
Thanks as always
Mike
I have created a form that with an image control called imageview with the following code.
Private Sub Form_Current()
Me.ImageView.Picture = Me.AlbumCover
End Sub
Albumcover is a text field containing the path of the bitmap.
All is fine unless i move to a new record in which i get an invalid use of null error on the code. Can someone help me remove this.
Thanks
Hi all, long timer viewer, first time poster
Any way, im not nearly as experience as all you, I have a small form, on the form I have a combo box, the form shows the relevant info with regards to what is selected in the combo box, the source of the combo box is a table, the data in the table is alaphabetical, but when you select the combo box the data shown to select from is not alphabetical its all over the place!
Hope that explains it, its driving me nuts!
Hello,
I have a very strange problem:
I have a table linked to an SQL Server table in my MS Access 2000 Application containing 5 text-fields (SQL Server Type, in Access seen as MEMO-fields) and several other fields (in total about 140 fields). Updating any field works fine except for some records. In those records I can only edit the non-text-fields in my application. When I try to edit one of the text-fields I get a error message telling me that another user has edited the record and i have to discard my changes.
The strange thing is that this problem only crops up for some records. (1 in a 100 or so) All other records work fine.
Does anyone know what happens here? And how this can be fixed?
Thanks.
Kaine
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?
Hi,
Please bear with me here as it's a little involved.
I'm doing a staff profile website which includes a section where they can enter their annual/other leave details.
I decided to store their leave in two fields Start_Date | End_Date rather than each individual date that they took - the short and wide approach vs long and narrow.
This has left me needing to do a query that would return all the dates between the start and end dates inclusive.
Example:
StaffID---Start_Date---End_Date
---1-----12/12/2004--14/12/2004
Returns:
StaffID---Leave_Dates
--1-------12/12/2004
--1-------13/12/2004
--1-------14/12/2004
I appreciate i could do this using some script to loop through a recordset and build an array of dates but i wondered/hoped that it could be done using SQL.
As it is an asp page i can't use user defined functions in a VBA module in Access so the solution would need to be pure SQL.
Is this possible?
Any help v.much appreciated.
TS
I have a scenario where the first three rows of date which have dates of 4/1, 4/4/ 4/6 with ndc 5513026701; next six rows that have dates from 4/8 to 4/20 with ndc 5513014801; next three rows that have dates from 4/25, 4/27, 4/29 with ndc 5513026701.
The issue I am having is I do not know how to have separate min/max dates for ndc 5513026701 since when I group by ndc 5513026701 min = 4/1 ; max = 4/29. I need to have min = 4/1 and max = 4/6 for one row and another row of min = 4/25 and max = 4/29.
Any easy way to sequentially create min/max for each ndc 5513026701? I wasn't sure how to verbalize this so I have attached a sample worksheet.....
I'm not sure if I am biting off more than I can chew. I have a text field in each record in my database (Inherited) The db has nearly 5,000 records. I would like to split the field into records in a seperate table. An Example of the table as is now;
Code:
MemberIDBoats
5882Opossum(78-80) (87-89) Otter(80-84) Opportune(91-93) Turbulent(97-00).
5883Astute Auriga Aeneas Affray Amphion
2407H34 O10 Porpoise Trenchant Tapir.
I want to create a table as follows;
Code:
MemberIDBoatFromTo
5882Oppossum19781980
5882Oppossum19871989
5882Otter 19801984
5882Opportune19911993
5882Turbulent19972000
5883Astute
5883Auriga
5883Aeneas
5883Affray
5883Amphion
Etc.
Is this possible in one hit or do I need to process the records without dates first and then run another process to split those with Dates? I say dates but the field is a text field. About 15-20% of the records contain dates which are always enclosed in parenthesis.
Is there a way in this program to create a list of dates between 2 dates?
i.e I have Arrival Date and Departure Date. Is there a function or expression that will list all the dates on and between?
I have a client that wants to enter a range of dates in a query of when they will call that person back. Then they want to be able to type in a range of dates and have a make table query show them all the people that fall in between these two dates....is this even possible???
Ex.
Joe March 3 to March 8
Mary March 4 to March 9
John March 5 to March 10
So if they type into the query March 3 to March 6 all three people should show up because one of the dates specified lies within the parameters they are asking for.....man I am out of ideas
Anyone.....
Hi
I want to add Hours to a date value.
For example the date value=05/04/2005 18:12:35
I want to add three hours to that date value so the new date value will be 05/04/2005 21:12:35. Is there operatior to add dates.
I want to search a table for records between two dates.
My code is like that
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str As String
Dim Sdate As String
Dim Edate As String
Sdate = Me.txtBeginningDate
Edate = Me.txtEndingDate
Set db = CurrentDb()
str = "SELECT ID, Date, Item, QtyRec, QtyIssue FROM DailyIssue Where Date >= '" & SDate & "' AND DATE <='" & Edate &"'"
Set rs = db.OpenRecordset(str, dbOpenSnapshot)
But it is not working at all. Error message is Data Type Mismatch.
Any help is appreciated in advance.
rahulgty
i've been reading about the us/uk date problem and found some helpful threads such as http://www.access-programmers.co.uk/forums/showthread.php?t=39675 but would like to ask (cause i'm still a bit confused):
if someone enters all of the dates into an .mdb the same way, either day-month-year or month-day-year, will the dates somehow be stored correctly regardless of the system's setting? (in this case, entered day-month-year into u.s. system-settings).
i have seen how access flips "wrong" dates. if i understand correctly, the dates are then actually stored "flipped", or wrongly.
is there some way of making the wrong dates (the ones that have been flipped) right again?
also, how does one view the dbl-precision number that is stored?