Over 1 million tech questions and answers.

Solved: MS Access modifying a date and time calc in separate fields

Q: Solved: MS Access modifying a date and time calc in separate fields

Hi there,
I have an MS Access database (2000, but primarily operating on PC with Access 2010). The database logs wagons coming to and from our site. Mostly, these wagons are in and out in the same day, but it is possible for them to park up overnight, for which we charge a small fee.

The situation has now changed slightly, in that we now accept wagons staying on site for longer periods (usually a weekend), so I've been asked to introduce an Exit Date field as well, and correct the existing calcualtion of time on spent site.

Currently, the following fields appear in the main table (called Stays):

Arrival Date
Arrival Time
Exit Time

There is also a field called Duration, which (for some reason) is autofilled by an Update Query, with the Update To value set to:

IIF([Arrival Time]>[Exit Time],1440+DateDiff("n",[Arrival Time],[Exit Time]),DateDiff("n",[Arrival Time],[Exit Time]))​

This is clearly inaccurate - it assumes that if a wagon's exit time is earlier in the day than its arrival time, then it must have stayed for 1440 minutes + the (negative) difference between the two times, and if the exit time is later or equal to the arrival time, the wagons has been in and out of site in the same day.

As wagons could be on site for multiple days (so the 1440 mins figure could be wrong) and could leave x amount of days plus x amount of minutes after arriving (so the assumption that it's exited the same day as it arrived could be wrong), I need to take the Arrival Date and Exit Date into account within this calculation.

RELEVANCY SCORE 200
Preferred Solution: Solved: MS Access modifying a date and time calc in separate fields

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: MS Access modifying a date and time calc in separate fields

Read other 8 answers
RELEVANCY SCORE 87.2

I cannot change entry in ID field of Facilities Table due to 1-1 relationship to Test Data Current Table. I just want to modify it from "70100" to "AI-70100".
 

A:Modifying Fields in Access 2010

Is there just the one record that you want to change?
 

Read other 3 answers
RELEVANCY SCORE 81.6

In an existing database, there are fields that have text I would like to separate (like first name and last name both in same field). Can this be done and how?

Thanks for your help!
 

A:Access - Can you separate text in fields?

Yes. You should probably start by reading up in Access help on the following functions:
InStr: Looks for a specific character, and returns its position in the string (data in text fields is called string data
Left([FldNm], x): Returns the leftmost x characters from a string
Right([FldNm], x): Returns the rightmost x characters from a string
Mid: Used to return part of a string, beginning from a midpoint, continuing a certain number of characters or to the string's end.

Access Help will explain to you how each one works in more detail. Basically, you test a field for a space using InStr; InStr will return 0 if it doesn't find one, or a position number if it does. If my name is "Joe Kool", for example, InStr will return 4. You then use Left() and Right() or Mid() to separate out the strings.

Let us know what more we can explain.
 

Read other 1 answers
RELEVANCY SCORE 79.2

I have a Date/Time field that I need converted to just a Time field.

It comes back with 2/11/12 0:00:00

I need just the 2/11/12

I thought about changing it to a text field but I think there will be an issue with the month because it does not bring back a 0 in from the months with only 1 digit.

This table that I am creating is being sent to a txt file and that is where I see the issue. It looks good in Access, but not the txt file.

Any suggestions?
 

A:Solved: Access - Convert Date Time to Date

Read other 10 answers
RELEVANCY SCORE 77.2

I have been trying to help a friend learn how to do form letters with his Microsoft Works Suite 2004 programs. He has Windows XP SP2. We were able to successfully merge desired fields from a Works database but noticed that the format of date and time field results was not as desired. The date field always merged as "MM/DD/YYYY" in the form letter no matter what format was used for date display in the database program. He wanted to display, for example, "November 12, 2004" instead of "11/12/2004". Likewise, the time field merged as "hh:mm:ss AM/PM" when he wanted, for example, "11:25 PM" to display.
In Works 4.5 (on another computer), his formatting carried over to the form letter from the database. Not so in the 2004 version. Our temporary fix, was to format the date & time fields in the database as "text" instead of "date" or "time" and then enter the exact display style wanted. Then, the merged value looked the same in the letter as it did in the database.
Is there a better solution to this problem?
 

Read other answers
RELEVANCY SCORE 73.2

Hello,

Firstly, I will try to explain what I am trying to do...
A couple of years back I started getting out and walking a few evenings a week as I was deperately in need of some excercise. At the start of last year I decided to keep a spreadsheet with the distance so that it would calculate the total distance and the Monthly totals (thought it might give a bit of incouragement ). This year the plan is to beat it (although the snow as meant it's not started well )

I have created a spreadsheet with the following columns...

A - Date: From cell A2 down I have entered the date. It is formatted to display as 01 January, 02 January etc but behind the scenes its showing as 01/01/10, 02/01/10.
B - 2009: This is where I have entered the mileage walked for each date listed in column A.
C - 2010: Same as column B but for 2010.

I can get it to calculate totals for each month etc but I want to display the total for each year upto todays date.
i.e. If I was to go into the spreadsheet today it would display the mileage walked in the 2009 column upto the 8th January and the same for the 2010 column.

TIA
Slimboy Fat
 

A:Solved: Open Office Calc - SUM Upto Todays Date

I have been trying to find the answer to my own question and have come up with this formula which seems to be heading towards my answer...

=LOOKUP(TODAY(); A2:A366; B2:B366)

What this does is look for todays date in the 1st column and returns the contents of the relevant cell in the 2nd column. ie. Today (09/01/2010) is found in cell A10 so the function will display whatever it finds in cell B10.
What I want it to do in this example is to do a SUM function between B2 and B10 (the cell which it found with the LOOKUP function).

TIA
 

Read other 3 answers
RELEVANCY SCORE 72.4

Hi.
On a Access form, I'd like to have a control that identifies how long the database has been in operation.
I'd like to be able to say that today is so many years, months & days since a specified date. Better yet, would be to include hours and minutes, or hours to 2 dps.

At present I can only count days, using:
=Date()-#13/02/99#+1 & " days"

I know I can use the DateDiff function as an alternative, and then I can specify the interval, e.g. I could specify hours as the interval and it would return the number of hours that have passed since that date.
But is there a way to specify multiple intervals?
I want it to return:

2 years, 1 month & 6 days

but certainly not:

2 years, 25 months & 761 days

which I imagine it would, if I entered all arguments into a single text box, using yyyy,mm,dd.

Someone on the Access Programmers site said that I should use DateDiff and then compute what I want from that result.

Does this mean I need to do separate controls for each interval?
And how would I 'compute' them?
Gram
 

A:Solved: Access: Elapsed Time Since Specified Date

Read other 16 answers
RELEVANCY SCORE 72

Hello.
I'd like to see Long Date and Long Time in my System Tray instead of Short Date and Short Time. 
But due to some restrictions connected with my work I can not modify Short Date & Short Time to look how I want them to look in System Tray.
So basically I need Windows to show what is set as Long Date & Long Time in System Tray instead of showing Short ones.

Is it possible? Maybe some registry tweaks?

A:How to display Long Date in System Tray (without modifying Short Date)?

Hi,
There is no workaround to display the Long Date in taskbar without modifying the Short Date parameter.Ivan-Liu
TechNet Community Support

Read other 8 answers
RELEVANCY SCORE 71.6

I have a field that is date/time, formatted to display the long date (which starts with the day of the week). This has been extremely useful for some append, update and delete queries.

However, I would like to be able to do an update query based on the day of the week for each record. I know that the date/time field is stored as a number so I can't search for any string of letters. But, is there a way to take the long date format and copy what's there as a string so I can then extract the day of the week out of it? Otherwise, I guess I'm stuck with adding a separate field for day of the week, and manually inputting it. Seems like such a waste.
 

A:Solved: Access, Date/Time Field to String -- is it possible

Read other 7 answers
RELEVANCY SCORE 71.6

Ok, here is what I have.

An Access Database that will allow for "Trouble Tickets" to be issued.

The way I have it set up now is a Status. This tells if the Ticket is Open or Resolved.

What I also have is a "Closed Date" Field, what I am trying to do is set it up so that if the Status is changed to Resolved, it will put the "=NOW()" in the Closed Date field.

Any suggestions. Everything seems to work fine, with the exception of this.

Thanx in advance.
 

A:Solved: Access - Closed Date/Time Stamp

Read other 13 answers
RELEVANCY SCORE 71.6

The company I work for has asked me to work with the DB (as you may well guess in this economy I now have to learn how to work with Access DB. This DB was created by someone who had a book “Access 2000 for dummies” in 2002, I have since then converted the 2000 DB to 2007 accdb format. That being said, in a form that is used to track records-TS# is the primary key, they want a YES/NO or actual name- Completed *check box for yes and blank for no*. I created the check box as you may know that was easy enough. The problem comes in when I go to the next record the box remains in whatever state it was on the previous record and does not show up anywhere else. I am sure this is likely because there are steps that I am omitting or not aware of. Any help offered would be amazing as I continue to work on this train wreck.

-Mike
 

A:Solved: Access 2007 YES/NO field with time/date

Mike, the Check Box field has to be in the Table that the Form uses as it's Record Source. The Check box has to be "Bound" to that table's field, i.e. it's "Contorl Source" of the check box in design view must be the table field.
 

Read other 2 answers
RELEVANCY SCORE 70.8

Hi,
I run a 24hour business and need to follow monthly trends in product
sales. I would like to differentiate sales during regular hours (Mon-Fri, 8am -5pm) from non regular hours. How do I put that criteria in a query? I can do it for one day, but not those hours over an extended period. I am not techno savvy. Thank you.
 

A:Solved: Defining time and date queries in Access 2003

Read other 11 answers
RELEVANCY SCORE 70.8

Hello guys,

I have a report which has (User, Office, Problem, Date time open, Date Time Close, and Total Date time of job.) TotalDateTime calcultates the time from when it was open to when it closes.

The report shows the total of all jobs so i can print it out. At the bottom of the report i want it to tell me the total time of all the TotoalDateTime records, i.e:

Joe Bloggs Open-11/06/2011 10:30:00 Close-12/06/2011 11:25:00 Total- 1 Day, 55 Mintutes
Jack Black Open-11/06/2011 10:30:00 Close-12/06/2011 11:25:00 Total- 1 Day, 55 Mintutes

TotalOverPeriod - 2 Days, 1 Hour, 50 Mintues

I cant seem to make it total up...Anyone know how to do this?

Thank you
 

A:Solved: Access 2010 Total Time and Date Values

Read other 16 answers
RELEVANCY SCORE 68.8

Hello, I'm trying to figure out a way to calculate time fields in a database, I would like to know how to add time fields from two text boxes so that the amount of hours will display in a Total Hours text box. I am using two text boxes, Start Time and End Time, Total Hours is where the total will be displayed in amount of hours from the two time fields, an example would be 8:00 AM (in Start Time) and 10:30 AM (in End Time) and 2.5 being in Total Hours. On a form, when typed in the Control Source property for Total Time, =DateDiff("h",[Start Time],[End Time]) seems to work, but it is not inputting the sum in the main table where all of the fields are kept, is there any way the total can be done and kept in the main table? Also, I would like to include a Total Hours text box with the totals of the Total Time fields because I would like to add more text boxes on a form. Can somebody help?
 

A:Access 2000 Calculating Time fields

That's the only place you can do it is in a report or query. I don't think you can calculate directly in the same table for fields within that table. Try creating another table and calculate the date fields within that table. Is there a reason this time difference needs to be stored in a table?
 

Read other 2 answers
RELEVANCY SCORE 68.4

Tech Support Guy System Info Utility version 1.0.0.1
OS Version: Microsoft Windows 7 Professional , Service Pack 1, 32 bit
Processor: Intel(R) Core(TM) i5 CPU M 520 @ 2.40GHz, x64 Family 6 Model 37 Stepping 5
Processor Count: 4
RAM: 2993 Mb
Graphics Card: Intel(R) HD Graphics, 1272 Mb
Hard Drives: C: Total - 234274 MB, Free - 193142 MB; E: Total - 2048 MB, Free - 2010 MB;
Motherboard: Hewlett-Packard, 1722, KBC Version 29.36, CND037022T
Antivirus: McAfee VirusScan Enterprise, Updated and Enabled

I would like to add a formula that would automatically calculate a date field from one cell and add three years. See example attached, Thanks in advanced
 

A:Solved: Calculating Date Fields in Excel

Read other 7 answers
RELEVANCY SCORE 68

I am using Access 2010, I have certain fields on the form that need to be entered in Arabic and certain in English. I have set the keyboard language for the text box to be "Arabic" (for text in Arabic) and "English" for text in English. When I do this, it takes a little more time to move between fields. How can I overcome this problem. In Office Xp, I never had this problem.
 

A:Access 2010 - Moving between fields takes time

how did you set the thext boxes to use different languages? I have never done this so I need to configure the same to test, also have you gone through all the settings and advanced settings of language Bar, Regional settings etc..

how much more time are you talking?
 

Read other 3 answers
RELEVANCY SCORE 67.6

I have a field that reads 2009-12-30-15.42.22.81298 as text. i want to make in be a date of 12/30/2009 15:42:23 and ignore the milliseconds. Is this possible?

Thanks
Ted
 

A:Converting complex date text fields into date or numbers

Ted, what software are you talking about?
Are there always 8 values in the date part?
 

Read other 3 answers
RELEVANCY SCORE 67.2

My access database stopped calculating the age of people for some reason. When it calculates, it shows the correct age (say 40) but in the format of 1940. I am using the difference of the last time the person took a test vs. their date of birth. the code being used is:

=DateDiff("yyyy",[text125],[Date])

even if I change yyyy to yy it still generates a 4 digit age. I have also looked at the input mask and no matter what changes I make to it it does not change the out put. the input mask is as follows:

99/99/0000;0;_

Any help would be appreciated as our frustration level is running high with this problem!!!!
 

A:Solved: Access calc age

Read other 12 answers
RELEVANCY SCORE 66.4

EXCEL SOS!!! I have an urgent deadline which I'm likely to fail if I can't get my head around this issue so please please feel free to comment / offer suggestions...I'll try anything!

I have around 10 - 15 sheets within the same excle file which is effectively acting as one tab per resource within my consultancy team. These are acting a a record of the billable & non billable hours per resource and therefore each line is primarily driven by date (2011 - 2013), with 1:N ratio items of the following: Client, Contract, Role, Days & hrs (effectively 1 line per date, resource, client & contract combination + time billed within the std 8 hr working day).

I have no problems creating individual pivot tables in the individual resource name's relevant tab, however the next step is to roll this information togather to show a holistic view for all resources, all clients etc (in what I anticipated to be a pivot table via the consolidation of multiple ranges function) for all resources by exactly the same fields:

Year, Date, Contract/Type, Resource, & Date - at row level (with possible additional options of Month, Week, Day being added as a row label)

AND

CLIENT , Non Billable - at column level

Naturally there may well be some slicing and dicing of the info needed to understand the figures at different levels, ie per resource (days billed & rate), how many hours used per contract, how we've used time in a contract (by role), & whom (by... Read more

A:Solved: Pivot multiple sheets for text, date and decimal fields

Hi there, welcome to the board!

I feel your frustration. Unfortunately your data structure isn't conducive for a PivotTable data model. But there is hope! You have some options here. First of all we need to know what version you're using. If you have 2010, well, you're going to really like what I'm about to tell you, and if you don't have it, it's a good reason to upgrade! 2010 has a new add-in called PowerPivot. Think of it as PivotTable's on bulk steroids. The benefit you would see - the ability to select multiple data sources. This means you can have multiple data tables/sheets as your data source, and select their relationships. Think of it like an Access table/relationship. It's the best feature we've seen in Excel since the PivotTable itself.

So if you have 2010, this is good news. However, if you don't have 2010, we need to consolidate your data to a single data structure. Whether that is putting it all on one sheet, or exporting to Access, or whatever, that's what needs to happen.

I'm not entirely sure what you mean about not getting the row level structure. Is there any way you can post the file?
 

Read other 2 answers
RELEVANCY SCORE 65.2

Does anyone have a tool that will extract the ID3 tag from an MP3 file on a redhat linux server so I can use the fields as descriptions for the mp3.

Thanks
 

A:Extract ID3 tag from mp3 to separate fields

Check this out: http://massid3lib.sourceforge.net/
 

Read other 3 answers
RELEVANCY SCORE 65.2

I have the following data shown below. I need to make a scatter chart and I am able to do so. However, the time (the first column) defaults into starting at 12.00 am. I want the chart to only reflect from 8.53 (which is the earliest time on my data list). I can't seem to figure out how to modify this time.

Emphasis 8:53 0.305 8:53 0.31 9:03 0.33 9:03 0.33
 

A:Solved: Excel 2010 Scatter Chart - Modifying Start Time.

Read other 7 answers
RELEVANCY SCORE 64.8

Hi all,

I've been struggling with this for a while, so I hope someone can help.

I'm using Access 2007 and have am using a TOTALS query which has a column that returns the SUM of HoursWorked

My problem is that I want to add a column which calculates two columns in this query and gives me the result of Total_Invoice: Rate * HoursWorked

Obviously I need the SUMMED value of hours worked in this calculation.

Is this possible?

Note:
I've tried changing it to a MAKE TABLE query and trying to do this calculation in an update query, but I can't get an update query to update the Total_Invoice column based on Rate * HoursWorked

Any and all help greatly appreciated.

Rob
 

A:Solved: Access 2007 Query : Calc based on Sum

Changed the query to a Select query (as it was a Make Table query).
Then ran another query based on the first one and was able to perform the required calculations that way.

If it is possible to do it within a single query I'd be happy to know how!

Rob
 

Read other 2 answers
RELEVANCY SCORE 64

I am using switchboards as an efficient way to generate an attractive menu system for an Access application. It is easy to do but the choices are limited. For example, one can open a form in edit mode but not add a where clause to show a certain record. I have noticed that the switchboard uses a table to store the commands and arguments. Is it possible to go into the table and modify the argument to include more than the form name? Is there a simple way to avoid using switchboards so the buttons can have more flexibility but still have the work be easy and the result look equally polished?
 

A:Solved: Modifying Switchboard Commands in Access

The keyboards use Visual Basic so if you open the Switchboard "Form" in design mode and then click on a button, in properties under the "Events" tab click the "On Click" event procedure then the 3 little dots on the right it will bring up the buttons VBA (and all the others). Alternatively open the database and press Alt + F11 and it will bring up the VBA editor, double click the Switchboard listed in left hand column and it will display it's VBA.
You can now modify the visual basic to do want you want.
 

Read other 2 answers
RELEVANCY SCORE 63.6

Dear tech experts,

I can't access date/time adjustment, and control panel. I even have smily at the right of my clock. Can u pls help me? I have HijackThis and ComboFix log file. I appreciate your help.



OS: XP SP2
Normal Mode

-------Hijack This Log File---------------------------

Logfile of Trend Micro HijackThis v2.0.2
Scan saved at 3:58:35 ('_'), on 4/7/2008
Platform: Windows XP SP2 (WinNT 5.01.2600)
MSIE: Internet Explorer v6.00 SP2 (6.00.2900.2180)
Boot mode: Normal

Running processes:
C:\WINDOWS\System32\smss.exe
C:\WINDOWS\system32\winlogon.exe
C:\WINDOWS\system32\services.exe
C:\WINDOWS\system32\lsass.exe
C:\WINDOWS\system32\svchost.exe
C:\WINDOWS\System32\svchost.exe
C:\Program Files\Intel\Wireless\Bin\EvtEng.exe
C:\Program Files\Intel\Wireless\Bin\S24EvMon.exe
C:\WINDOWS\system32\spoolsv.exe
C:\Program Files\Intel\Wireless\Bin\ZcfgSvc.exe
C:\WINDOWS\Explorer.EXE
C:\WINDOWS\System32\igfxtray.exe
C:\WINDOWS\System32\hkcmd.exe
C:\WINDOWS\SOUNDMAN.EXE
C:\Program Files\QuickTime\qttask.exe
C:\Program Files\Java\jre1.6.0_05\bin\jusched.exe
C:\Program Files\Eset\nod32kui.exe
C:\WINDOWS\system32\ctfmon.exe
C:\Program Files\Messenger\msmsgs.exe
C:\Program Files\Skype\Phone\Skype.exe
C:\Program Files\Google\Google Updater\GoogleUpdater.exe
C:\PROGRA~1\Yahoo!\MESSEN~1\ymsgr_tray.exe
C:\Program Files\TypeItIn\TypeItIn.exe
C:\WINDOWS\system32\SupportAppXL\cdrom_mon.exe
C:\Program Files\WIDCOMM\Bluetooth Software\bin\btwdins.exe
C:\... Read more

A:Can't access CP and date/time, pls help

See if this gets back your control panel..

Copy the text the in the code box to notepad. Save it as fixreg.reg to your desktop.
Be sure the "Save as" type is set to "all files"
Once you have saved it double click it and allow it to merge with the registry.



Quote:




REGEDIT4
[HKEY_CURRENT_USER\software\microsoft\windows\currentversion\policies\explorer]
"NoFind"=-
"NoFolderOptions"=-
"NoControlPanel"=-

Read other 18 answers
RELEVANCY SCORE 63.6

I have an Access 97 database on the network, in which about 10 users share. There is this one form that updates returned goods, for some reason I have one computer that adds the time and date to a date field. I want to get rid of the time, and leave short date. It looks like this, 2/5/2001 10:47:50 AM
Data is missing when I query the database using date ranges. I'm sure it's something that I'm missing. Any suggestions? TIA

This is the table input mask 99/99/0000;0;_
Table Format mm/dd/yyyy
 

A:Access 97 Date And Time

Check the user's regional settings for time/date...that's my guess.
 

Read other 2 answers
RELEVANCY SCORE 63.6

Hi all,

I?m really in need of some help from some Access experts! I?m a newbie to Access, but I got myself busy with quite a big project... I?m trying to map out the product supply to shops in the whole country. I?ve come quite far for a starter, but I keep having 3 questions, it would be great if you could help me with this!

I have, amongst other tables, two tables called Stock_counted and Reported_quantity.

The table Stock_counted has the following fields:
- Shop_number
- Product_code
- Quantity_counted

The table Reported_quantity has the following fields:
- Shop_number
- Product_code
- Quantity_in
- Quantity_lost_or_returned
- Quantity_out

1. What I would like to do is to make it one table based on Shop_number AND Product_code. So in one record I would like to have Shop_number, Product_code, Quantity_in, Quantity_lost_or_returned, Quantity_out and Quantity_counted. How do I do this, making sure that the quantities end up behind the right product and shop? It is possible that some shops don?t have all items, while other shops might not have all products reported, or both. On top of that, I have another table with a price for each product, which needs to be related to it as well.

2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?

3.... Read more

A:How to combine fields in Access 2010 based on 2 equal fields?

  
Quote: Originally Posted by Josephine87



2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?



select shop, product, sum(quantityfield1), sum(quantityfield2)
from sometable
group by shop, product

Read other 1 answers
RELEVANCY SCORE 63.2

I haven't been able to quite get my functions right on this one and am hoping for some guru help.

I have a spreadsheet with dates in Column A (Contract Received) and Column C (Contract Filed). Not every cell has a date in one or both fields, as it is a tracking tool for work in progress. However, a contract must be Received before it can be Filed, so C will (er,should) never be blank if A is populated. I want to know the number of days lapsed between date Received and date Filed, and if date Filed is null, then number of days from Received to today's date. If the contract has not been Received, then no calculation should be performed. In spite of requests not to, users sometimes put text notes in these fields, so would be nice to ignore text. At bottom of column, I need to get an average of the time lapse on all filings to track productivity. This doesn't sound so difficult to me, but somehow I haven't gotten it quite right in the last two hours and it's time to ask an expert. Thanks in advance for your help!

- k
 

A:Excel 2010 Date Calc Formula, Ignore Blanks

Hi k, welcome to TSG.

Try this and let us know if it gets you close to what you need:

=IF(ISNUMBER(A1),IF(ISNUMBER(C1),C1-A1,TODAY()-A1),"")

It basically starts with column A and determines whether there's a number there (a date, as opposed to text or blank). If not, it doesn't do anything, but if so, then it does the same thing with column C. If C has a number (date) then it subtracts A from C. If not, it subtracts today's date from A.

Let me know if I've misunderstood the question.
 

Read other 1 answers
RELEVANCY SCORE 62.8

I'm working on a date/time calculation to put on a MS Access form. My database field names are start and end and what I am looking for is the difference between the two. The start and end is a date/time format. I'm also interested in putting this in a report. Any help would be greatly appreciated.
 

A:MS Access Date/time Calculations

Read other 12 answers
RELEVANCY SCORE 62.8

Last week in a database I was developing I had 2 Date/Time fields set with General Date format. It was working perfectly last week where I would type in 10/24 12a and it would expand it out to 10/24/2007 12:00 AM for me.

Fast forward to today and it is now only recording the Date and not the time. I have changed nothing and on a rare occasion can get it to recognize the date and time in the datasheet view but never from the form.
Anyone else ever witness this and know the fix?

Thanks

DPH

Edit: Well I found what I believe is the issue.

If I type 10/26 11p it will convert to 10/26/2007 11:00 PM so the user can short hand the entry.
If I type 10/26 12a it will only show the date. So 1 minute around the entire clock will error.
 

Read other answers
RELEVANCY SCORE 62.8

I'm using Access 2K.

Kindly let me know how I can log the date/time of the the last time a record was edited.

To log the name of the last user that edited a record, I created field in my table named "LastEditedBy". In the form's "BeforeUpdate", I'm using the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
LastEditedBy.Value = CurrentUser()
End Sub
 

A:Access 2K: Date/Time Of Last Edit

Read other 8 answers
RELEVANCY SCORE 62.4

In moving from Access 97 to Access 2002, my Date/Time filter doesn't seem to work. In Access 97, I could select a particular record using the a field with a Date/Time format using the Advanced Filter/Window as follows:

Field: Begin_Time
Criteria: =#03/26/2007 1:06:18 PM#

This date/time was copied directly from a record, so I know it exists, but the filter produces no records. If I substitute any other logical operation, like >, >=, Between, the filter works ok. But = does not. All of this works in Access 97.

Any help would be appreciated!

datasmith
 

A:Access 2002 Date/Time problem

Read other 13 answers
RELEVANCY SCORE 62.4

Is there a way to cut the "time" function from the "Short Date" format? When I set up a table export the 00:00:00 keeps showing up next to the dates. I can move the column demarcation lines (fixed width markers) to drop the zeros but get the "export errors" message.
 

A:Access 97- edit time from Short Date?

Read other 6 answers
RELEVANCY SCORE 62.4

I run a query for a quarterly report that has 8 fields, 2 of which are dates. The dates have to be in the mm/dd/yyyy format. When I run the query everything displays correctly. when I save the data to a table everything displays correctly. However, when I export it to a comma delimited txt file, the time also displays: mm/dd/yyyy 00:00:00. I checked the formatting for the 2 date fields, and they are both the 'Short Date' format with an Input Mask of 99/99/9999;0;" ". I also tried using other placeholders, which didn't work.

I can get rid of the 00:00:00 if I export it as a tab deliminated file, but then I lose the commas, which are a requirement. The report has to be a .txt file, with strings wrapped in quotation marks, dates (no time) in the mm/dd/yyyy format, and fields separated by commas with no spaces in between.

Are there any other places I should try to change the format, or tricks to make sure the date and only the date print? Or is it an Access glitch and I need to do a re-install? Thanks for any help.
 

A:Access 2K Date/Time Format problem

Add a column using the expression left([date],10) and export the query.
 

Read other 1 answers
RELEVANCY SCORE 62.4

Hello,
I'm setting up a database and have several date/time fields, which I'd like to have in a custom format - Short Date (UK) plus Short Time format, with a hyphen separating.

I'm trying the Input Mask:
00/00/00" - "00:00

I also tried editing the Date Format to:
dd/mm/yy - hh:nn

But either option (or both) gives me an error message that "The value you entered isn't valid for this field".
I've tried a couple of variations, but I'm evidently missing the principle of how this works.

I'm sure I've done this before, but can't remember how to fix it - oh, and I can't really mess with the PC's regional settings.

Thanks
Gram123
 

A:MS Access 2002 Date and Time format

Sorry I cannot work out how to do it either. I can suggest that you use seperate fields to
input the date and time if you have to input them, why not just use the system time?
 

Read other 3 answers
RELEVANCY SCORE 62

Have set the correct time zone, set the date and time, and within half an hour, the time jumps ahead significantly. Time then continues jumping periodically, and date as well. Any patch available for this?
Thanks.

A:Date/Time jumps after correct setting of date/time/timezone?

Welcome to the Seven Forums

Perhaps a change of cmos battery is needed. What is happening is a sign of the battery having packed up.

Read other 2 answers
RELEVANCY SCORE 61.6

Hi,

I am running XP Home SP3 on an Asus 1005HA netbook

Every time I boot up I get a message to say that either I can press F1 to enter setup to set date/time or press F2 to load default values

If I choose F1 and enter the correct date/time and press F10 to save and exit - XP then starts up normally but the next time I boot up, the same problem occurs.

[If I select F2, then XP loads normally]

What can I do to fix this?

Thanks for any help in advance
 

A:Solved: CMOS Set date/time message every time I boot up

Your motherboard battery is dead. You can get a new one at any drug store for about $5.
 

Read other 3 answers
RELEVANCY SCORE 61.6

Looking for a utility that will allow me to locate files created/modified/accessed using TIME from/to also...as i want to locate those related to a specific occurance. When using just date, I get hundreds of files so would like to isolate further.

Is anyone aware of any such program? Shareware might be ok depending on cost but free is preferred. THANKS!
 

A:Solved: Freeware to find files by date and TIME? Time is the key!

Read other 10 answers
RELEVANCY SCORE 61.6

Hi all,

This is probably a serious newbie question, but I found myself banging my head against the wall last night and still couldn't figure it out. It concerns the Date/Time datatype. I've been developing a media database and I've included a field of this datatype to hold the Year that my books are published. However, all I want is the year. I don't want the day or the month. But this datatype requires that I punch in the day and month as well. I don't want to do that. I just want to punch in the year. How do I alter what's required in the field?

FYI, I did figure out how to make it display only the year using Format, but I still have to put in the day and month. . .

Many thanks in advance. I'm sure the answer is right in front of my face and I'm just not seeing it.
 

A:Access 2002 Date/Time datatype question

It seems straight forward...just make a 4 digit field for year forget about the date/time data type, just use "Number or text type".

if you stick with the Date/Time datatype you can use the "Year" function in an expression to extract the year from the date field.

eg. YEAR([YourDateField])
 

Read other 2 answers
RELEVANCY SCORE 61.6

I'm working an Events Management Database with a table for Events.

The table contains a field for EventDate (date field) and StartTime (time field).

My problem is I need to come-up with a way to prevent users from double-booking on the same day. In other words, prevent them from entering the StartTime of a date twice - on the same day.

Is there code I could use on a form for this?


On another note I love the new layout of the website... Good job!
 

A:Access 2003: Prevent Duplication Of Date/Time

Read other 16 answers
RELEVANCY SCORE 60.8

Hi,

I have a form that needs to be filled in for several hundred letters, containing (legacy) checkboxes and (legacy) text form fields.

Now, I have found a macro/add-in that will allow me to perform a mail merge and save each letter created to a separate word document (link here http://www.gmayor.com/individual_merge_letters.htm ).

What I can't find is a way to create those merged letters/documents with the text form fields intact -- according to MS in this article http://support.microsoft.com/kb/286841 performing a mail merge removes all text form fields, but they provide an example of a macro that will perform a mail merge that retains the form fields BUT it outputs all the letters to a single document, while I need to spit each letter into a separate document.

Could anyone help me combine the two macros so that I can get my form *with text fields* mail merged to *separate* documents? I have no idea how to code in VBA unfortunately... I am using Word 2007 on XP if that is of any relevance.

Any assistance is greatly appreciated
 

A:Saving individual mail merge letters to separate documents with text form fields?

Can you post a sample document?

Regards,
Rollin
 

Read other 2 answers
RELEVANCY SCORE 60.4

I would like to "Build" an expression for a date/time field that will query the date and display my query output as "01/01" or "January, 1st", and leave off (omit) the year.

Im trying to print a report of my employees birthdates without displaying the year, so i can post it publicly.

the name of the column is dob (date of birth) and the format is standard date/time, displayed normally as 01/01/2009 etc ....

The best idea i could come up with so far is

= Format (Date(), "dddd, mmm d")

but this displays todays date and not the date stored in my column lol.
 

A:Access 2003, want date/time to show day and month only (& no year) in query

Read other 7 answers
RELEVANCY SCORE 60

I have to concatenate the following by item number: I have attached a file with a sample table.

item number / memo subject / memo text
15056 / x/ e
15056 / t / r
15056 / s / q

there is also the issue where I have ~;~ in the memo text field. This is coming from some type of code in our ERP software. I would like to have this removed and not added in the final result which would be something like this:

15056/ x t s / e r q
I do not need comma's in between the data but if the field is blank i don't need that either.

I have researched and I have tried to do some VBA with Modules but I really don't know what I am doing.... Help!
 

A:Solved: Access - Concatenate fields

Read other 16 answers
RELEVANCY SCORE 60

Please help. I am trying to calculate a percentage of my yes's. I have several yes/no fields in which I'll need to calculate a rate. I would prefer to create a report that will calculate this for me. However, right now, I am really just looking for the easiest/quickest way to do this. So if I need to run a query first and base the report off the query, I'll do it. Please help ASAP if you can.

Thanks,
Tonya
 

A:Solved: Calculating Yes/No Fields in Access

Read other 16 answers
RELEVANCY SCORE 60

I have a database, and I already have several fields "locked" so that chances cannot be made to these particular fields.

I have some command buttons that I currently have enabled, but would like to disable them when a certain checkbox has a "true" value (is checked).

How can this be done (if at all) on a "per button" basis, as I have some other buttons that I want to leave enabled all the time.

Thanks in advance!
 

A:Solved: Locking fields in Access

Read other 9 answers
RELEVANCY SCORE 60

I have an Access database where the data is being input in Access and displayed by Lotus Notes. Originally I had a number of Yes/No fields e.g. "Outside Storage" in the table. The person doing the Notes interface requested that I change these to Text fields which I did. The table values remained as Yes or No when I did this. The input forms kept their check boxes when I did this but as one enters data via the checkboxes the values are 0 and -1. If I delete the field from the form and add it again it comes as a text box which then displays Yes or -1, etc depending what is in the table.
The Notes person says data tables should not contain Yes/No fields or Combo Boxes like solid/liquid. My question is what is the best way to handle this assuming the Notes developer wants text fields in the table? Should I delete all the checkboxes from the forms and replace with text boxes showing Yes or No? Right now I have the validation of the table set to accept yes, no, 0 or -1. Finally, is the field on the form able to be changed back to a checkbox without changing the table field back to a Yes/No field? It seems as if the type of field placed on the form is inherited from the table.
 

A:Solved: MS Access Yes/No or Text Fields?

Read other 7 answers
RELEVANCY SCORE 60

I have a fully functioning dbase with the last Calculation that I cannot work out.

I am attaching a sample Dbase to help w the explanation.

I would like the Bonus Total to reflect all Totals. After totaling rate and standby total, I then want to choose my services and total out the Services total and refresh the Bonus Total with all of the updated info. I have stared merciless at this and tried several ways to make this happen.

I am slowly going mad. This is the last calculation then I am finished with this but have gone blank.

If anyone can help me, I would appreciate it.

As you tab through the dbase you will see that the Base rate is divided by 2 to get the standby rate and this reflects automatically in the Bonus Total in the top half of the dbase. Then you progress to service selections and the total is reflected upon pressing enter in the Services Total. This Total is what I want added to the existing Bonus Total that is calculated in the top of the dbase.

Sorry if I am repeating myself.

Anyone?
 

A:Solved: Access, Calculated Fields, OBP?? Anyone?

Bonus total has the calculation =[cboRateTotal]+[cboStbyRateTotal]+[cboServicesTotal] as it's control source.
Once you do that you can no longer change the value using VBA.
So the answer is to replace the =[cboRateTotal]+[cboStbyRateTotal]+[cboServicesTotal] with the VBA version.
So either set the control source to nothing or make it a field in the table.
 

Read other 2 answers