Computer Support Forum

Solved: Access 2010 Set a date field to today plus 30 working days

Question: Solved: Access 2010 Set a date field to today plus 30 working days

When items are loaned out we want them returned in 30 working days (approximately 6 weeks I'm not going to bother trying to calculate holidays) but to only come back on a week day Monday through Friday.

Field Name ReturnDate_Fld this needs to be calculated as 30 working days from the current date

or should I not worry about the extra effort and use the approximate by adding 42 to the BorrowDate_Fld (plus 42 days = 6 weeks)

Should I worry about this in a form or only set it as field in the report/query?

Relevance 100%
Preferred Solution: Solved: Access 2010 Set a date field to today plus 30 working days

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.)

Answer: Solved: Access 2010 Set a date field to today plus 30 working days

16 more replies
Relevance 76.85%

I want to add a validation rule to a date/time field in Access 2010. This worked in 2003 and I'd like to know why it won't work now.

The rule is

Is Null Or <= Date()

I have entered this through the expression builder as well as typing it to avoid typos.

The error message is:

Unknown function 'Date' in validation expression or default value on 'Artists.Date edited'.

I am doing this at work: we are running Windows 8, Server 2012, Access 2010 via virtual desktop.

Any help would be gratefully received!

Thank you
Alison
 

Answer:Solved: Access 2010 date field validation problem

Alison, welcome to the Forum.
I think you will find that this is an Access problem of Access 2010.
You may be able to overcome it by using Now() instead of Date().
If not I can provide you with some simple VBA code that will do the same thing that may work.
I have also seen advice to open a new 2010 blank database and import everything in to it to solve this problem.
 

2 more replies
Relevance 100.45%

Hello,

I have been struggling with this for a couple of days.

Here's the scenario: I have 2 sets of data, one shows repeat calls for a list of customers, the other shows trouble reports for the same set of customers. I want to count how many times each of these customers called within 30 days of the original trouble report.

I have tried using a nested count Iif expression but I don't have a lot of experience with expressions and apparently am using it incorrectly. What I have tried is something like this:

=Count(IIf([Contact Date/Time],+30,1))

Any suggestions or ideas are welcome at this point. Thank you in advance!

Thanks,

sdl19
 

Answer:Access 2013 - Count only if date falls within 30 days of field from different table

I would create a 'groupby' query using the DateAdd function for criteria. If this is a report you should be able to link this query to the report query by acct number or something.
SQL looks like this example
SELECT Table1.Name2, Count(Table1.ID) AS NameCount
FROM Table1
WHERE (((Table1.Date1)>DateAdd('d',-30,Date())))
GROUP BY Table1.Name2;
 

1 more replies
Relevance 97.58%

I have a Date field in a Form. I want the Date to be appear in red when it is more than 6 months older than today's date. Where and how do I make that happen?
Thanks
 

Answer:Access 2010 Date Field in Form Macro or ()Iff ??

10 more replies
Relevance 90.61%

I have a field for data a appoinment is for in an access table and i need to apply validation to allow entry of todays dat or anydate after today.
I have tryed stuff like ">=Now()" or ">Now() and =Now()" but nothing works please help
also can anybody help with my other problem about No changes to certain field in a table or form
see

http://forums.techguy.org/business-applications/495256-access-no-editing-certain-field.html
THANKS
 

Answer:Solved: Access Date Today or after today

If this is the same field as the first post then you will be stuck with the default value of Now() or Date().
If it is a different field then in the validation property use >=Date().
Date() is used when you do not need to store the "Time" as well as the date, Now() stores the Date and the Time.
 

2 more replies
Relevance 86.1%

from email address removed by Dreamboat

I have a database with a date field; 'Birthday'.

I want to take the month info from the date field and have access put it into another (adjacent) field. Ie;

Name:
Addr:
Birthday:
Month of B'day (New Field)

Also I want to be able to query the birthday field from previous data base tables which do not have the month field and make a table with the month information extrapolated to a new 'Month of B'day' field.

(Month([Birthday])) does not work.

I have printed a report with the 'Month of B'day" info extrapolated to the report but it is not useful because the report does not sort by the new information.

I know these must be possible but . . . . . AAAaaagh I'm going nuts.
 

Answer:Solved: Extrapolate Month from an Access date field

16 more replies
Relevance 86.1%

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.
 

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

7 more replies
Relevance 86.1%

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
 

Answer: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.
 

2 more replies
Relevance 85.28%

I've searched the forum and can't find the help I need...perhaps I'm not using the right keywords.

Anyway, I want to automatically have a time stamp entered in a field named "CancelDate" if a yes/no field named "Cancel?" is checked. On the form I use an action button for that field.

I'm new with VBA, but after some research I entered this code in the AfterUpdate line in Properties:

Private Sub CancelDate_AfterUpdate()
If Me.txtCancel?.value = 1 Then Me.txtCancelDate = Now()
End If
End Sub

...and it doesn't work. It seems to hang up because of the question mark. I get a Compile Error: Expected:Then or GoTo.

Can anyone help me?
 

Answer:Solved: Access 2007 Record Date When Field Updated

10 more replies
Relevance 85.28%

Good day guys

My puzzler today (and has been for ages) is this:

In my Access report, I pull in "StartDate" and "EndDate" fields from the calling form and wish to display them as part of a report subtitle.

I therefore have a field called "DateDetails" in my report header whose ControlSource is:
Code:
="Records Selected between " & Forms!Reports!StartDate & " and " & Forms!Reports!EndDate
The dates actually derive from a table and the date format for both fields in that table is "General Date".

The result is that the report shows the following as an example:
Records Selected between 17/05/07 12:00:07 and 10/08/07 12:00:07

BUT what I want is the following:

Records Selected between 17/05/07 and 10/08/07

or even nicer

Records Selected between 17-May-2007 and 10-August-2007

Can this be done in such a control?

Chris
 

Answer:Solved: Access 2003 - Date Format in a Calculated Field

7 more replies
Relevance 85.28%

Good day,

I'm seeking assistance with glitches in my MultiValue Lookup Boxes:

Scenario No. 1:

The MVL field is sorted in alphabetical order, last name first.

Example of names in MVL:
1. Michael, Christopher
2. Michael, Jones

When I search the MVL field for Michael, Jones it returns the record for Michael, Christopher. Even if I manually scroll the MVL field and click on Michael, Jones, I’m still presented with Michael, Christopher’s record. It appears the MVL field is only searching/looking at the first occurrence of Michael.
Scenario No. 2:

The MVL field cannot locate records with an apostrophe in the name. For example: Jn’Marie. Do I need to omit the apostrophe?

Thanks for your assistance.
 

Answer:Solved: Access 2010 MVL field Errors

16 more replies
Relevance 85.28%

I have inserted code into the BeforeUpdate event that tells a user if a particular field, which has been skipped, is required. The code works fine, but when you click the "OK" button so that you can proceed to input the required field, you get (1)a window that tells you "You can't save this record at this time.....do you want to close the db object anyway?" You click "no" so you can proceed and you get (2)another window titled "Macro Single Step" . I then have to "X" out of that window before finally being able to return to my form to input the required field.

Is there any way I can prevent (1) and (2) from displaying so that the user can simply go from "OK" back to the input screen and enter the required field?

I am using the following code"
Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me![niche]) Then
MsgBox "Niche number is required"
Cancel = True
End If
End Sub

The form uses a "Close Form" button.

thanks for any help.
 

Answer:Solved: Mandatory field in Access 2010

16 more replies
Relevance 83.64%

Hello,
Firstly, let me apologise, I'm a complete Access noob & am trying to teach myself as I've got no one to help me in person.

Ok,
I'm trying to build a Time Sheet database that should record daily duties via an entry form:
"frm_Work_Hours"

This form updates an underlying table:
"Work_Hours"

The date field of the "Work_Hours" table is:
"Date_Worked"

However, workers get paid fortnightly and all their entitlements, allowances & overtime are calculated on these fortnightly periods. To be more specific, the workers are paid for a certain amount of overtime for each fortnight upfront & when they exceed that, then they are entitled to all excess time back in TIME OFF. So it's pretty important that each day's duties are assigned to the correct fortnight period so that their overtime & so-forth are calculated correctly.

Each fortnightly period is numbered according to year. For example, each year there are 26 fortnightly periods, so as an example today (3rd October 2013) falls in period 2013-19 (23rd September 2013 to 6th October 2013).

I've created a table called:
"tbl_Pay_Period"

Which has the following Fields:
"ID" (Auto-number)
"Period_Start" (date - the first day of each new fortnight period)
"Period_Number" (number - eg: 2013-19)

Back in the "Work_Hours" table, I inserted a new (Number) FIELD called:
"Pay_Period"

and set ... Read more

Answer:Solved: MS Access - How to get a field auto-update based on date entered in a form

9 more replies
Relevance 83.23%

I have a database where I have a form that has a subform.

I'd like to conditionally format a field on the form so that if blank, it highlights grey. The form is set continuous.

I've been able to do this on a standard form but not on my sub-form.

What I can do (for instance) is set it so that if there is a 1 char length it highlights (achieved with Len(fieldname) = 1 but not for blank or zero length.

During debugging I've displayed text on the form showing the length of the field and it shows blank. If I enter something and then delete it, it then shows 0 and works.

I've tried Null, blank, <0 and still no go. Any ideas?

Is this a bug in Access?
 

Answer:Solved: Access 2010 Conditional format field on subform

I think I just solved this myself. For new items added it works but for exisiting fields with zero length it doesnt so I guess this is a bug
 

1 more replies
Relevance 78.72%

I want to know an exel formual for the following - Any help would be great

Date Range
01/12/2013 - 03/14/2013

How many days from the date range 01/22/2013 - 02/21/2013 are in the above date range

So answer for the above would be 31

Thank you for your help!
 

Answer:Excel 2010 - Count # Of Days In A Date Range That Are Part of Another Date Range

Hi LucianC - try entering this info:
Start Date in A1
End Date in B1
=DateDif(A1,B1, "D") in C1
 

2 more replies
Relevance 78.72%

I have a Word 2010 document and I want to have specific text appear if a date is more than 42 days away from today (whatever today happens to be). I've got a three-cell table that looks like:


________________________________________________________

| DATE: | 12/25/13 | { =IF(B1-TODAY()>42,TRUE,FALSE) } |
________________________________________________________



I keep getting a Syntax Error on my field in the 3rd cell. I think the problem is that Word doesn't understand "TODAY()" like Excel does, but I don't know if there is any other way to calculate the days between two dates? My eternal gratitude to anyone who can figure out how to do this properly.

Answer:Date calculation using Word 2010 table/field Syntax Error

You will need to create a spreadsheet then "Link" that spreadsheet containing the calc cells.

Things to Remember When Linking
If you move the linked Excel file, you break the link. To fix this, you need to relink the file.
Double-click on the Excel data in your document to edit it: this opens the linked spreadsheet.
You can also edit the spreadsheet directly: the changes appear in your Word document once you save the Excel spreadsheet.
Regards,
GEWB

3 more replies
Relevance 78.31%

First the background: We are gradually deploying Office 2007, but not everyone in the agency has it. I have both 2003 and 2007 installed on my system, but I do my work in 2003, and none of our databases have actually been converted to 2007 format (at least, none that I work with). There are occasionally people who use 2007 when viewing databases, but I don't know if any of the people who are working in this particular database are using 2007. The problem is reproducible in 2003 and so I've been trying to fix it here. The query behind the problem report used to work. It has been a problem for several months (it is run monthly, and every month for the past five they've had problems with it) and I've finally gotten to the root of the actual issue.

The purpose of the report is to generate invoice for outstanding loans. The DateNextPayment is a calculated field based on the payment plan for the loan. It is calculated using a function in the query named qryGetSitesDDInvoices1. This is the function that is called (I did not write this function, I'm supporting this db long after the original writer has left):
Code:
Function DateNextPayment(PaymentSchedule As String, DateFirstPayment As Date, Balance As Currency)

On Error GoTo Handle_err

Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim NextQtrDate As Date

'The code adds one month to datenextpayment at start of each month. Or yearly or weekly

... Read more

Answer:Solved: Date Parameter Failing in Query from calculated date field using DateValue Ac

16 more replies
Relevance 77.49%

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
 

Answer:Solved: Access 2010 Total Time and Date Values

16 more replies
Relevance 77.08%

Help

I'm mail merging with Word a huge contract document and need to put in the phrase 'this contract is made on the [seventeenth] day of [April] [2010]'. Even in a custom format I'm struggling. When the contracts are printed off they need to be 'today's date' ie the day they are printed.
Thanks
 

More replies
Relevance 75.85%

Hey guys,
I have a question about coding and setting an excel file to send emails. I am using an excel file to track maintenance work orders and I would like to get an email each day for each work order that is requested to be finished on that days date. I have attached the excel file I am using, and the column that is important is C, "Date Needed" - If that date is today's date, the cell turns red and I would also like to get email notification about it. A separate email for each cell that matches today's date.

Also, if possible, I would like for it to look at all the sheets, not just the current month's sheet. For example, if a work order was filed in July but wasn't needed until September, I would still like to get the email reminding me that it needs to be finished on xx date in September.

I have attached the excel file I want to use.

I have searched all over this forum and others and have found many posts similar to this, but I am not a great manipulator of code, so I cannot get any of the other solutions to work.

Any help would be greatly appreciated, thanks!
 

Answer:Solved: Using Excel 07 to Send Emails When Date in Column = Today's Date

16 more replies
Relevance 75.85%

Hello everyone,

I'm working on an order management database and I'm stuck on one item.

I have a form that users can fill in with purchase order data. One section of the form relates to contract review completion.

I would like to have a checkbox that the user can check when contract review is complete, but I'd also like for a completion date field to be auto-filled when the checkbox is checked.

I've tried a couple of things, both unsuccessful.

Any ideas?

Thanks.
 

Answer:MS Access - autofill date field

6 more replies
Relevance 75.03%

I want to combine a text field and a date field into one field to use as a reference number. IE 'Mil - 05/11'.

I have got a field with the standard short date format 29/05/2011 (that I have to keep) and I have created a second field with the same date but formatted to a "mm/yy". When I concatenate the Operater Ref field "Mil" to the Date Ref field "05/11" I still get the full date format IE Mil - 29/05/2011.
 

Answer:MS Access Concatenate a date and text field

Jerry-

If you are creating the second field in your table, go to design view and change the Data Type to Text then try your concatenation. I built a mini-table and queried it and this worked for me... hope this works for you!
 

19 more replies
Relevance 74.62%

Hello.
I am using some found (and slightly altered) code to allow me to calculate working days in MS Access queries.
My queries contain a number of calculations of date fields, monitoring things like "Arrival to Estimate", "Estimate to Authorisation", "Authorisation to QC Pass" etc
All was going swimmingly, until I got to a date calculation where one of the dates is null. That results in a #Error in the cell in the query, and the corresponding report fails to open, giving an error message - "The expression is typed incorrectly, or is too complex to be evaluated".

The code is as follows:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named Holidays with a field named Holiddate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [Holidate] FROM Holidays", dbOpenSnapshot)
StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above... Read more

Answer:Solved: MS Access VB working days #Error

I found a solution. Should anyone happen to care what that solution is...

Changed:
Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer​
To:
Public Function WorkingDays2(startdate As Variant, EndDate As Variant) As Variant​
And after:
Dim DB As DAO.Database​
Added:
If IsNull(StartDate) OR IsNull(EndDate) Then
Exit Function
End If​
 

1 more replies
Relevance 74.21%

I have a linked Excel spreadsheet to show invoices received and I insert a date when they are paid. I cannot figure out the expression to use to show all records where no date has been inserted. Can anyone help please.Thankyou

More replies
Relevance 74.21%

Hi guys, I'm very new to access and I'm having some issues. I have made a field for an expiry date, and I have attempted to create a calculated yes/no field that gets ticked when the expiry date is up, so basically I have a box that I want ticked for when the expiry date is before the current date (I hope that makes sense). Thanks guys,

George
 

More replies
Relevance 74.21%

Hello

Access 2003
Windows XP

I am creating a new table and would like to format a field to short date. Here is my code. Is there a way to format to short date from here?

Thanks

Set fld = tbl.CreateField("Date", dbDate)
fld.Required = True
tbl.Fields.Append fld
 

Answer:Access 2003 - Format field to Short Date

try this:
fld.NumberFormat = "m/d/yyyy"
 

3 more replies
Relevance 73.8%

I have a table titled time_slips used to track the amount of time an attorney spends with a member. In the time_slips table there is a field titled reference_id which is a combo box pulling it's displayed information from another table titled references. Also in the time_slips table is a field named details which is native to this table.

My question is, can I have the details field auto-populate "no charge" when a certain reference id is selected (i.e. 300)?

Thanks for any help you can provide.
 

Answer:Solved: Access Auto-Populate a Field using a field from another table

15 more replies
Relevance 73.8%

My database is for a movie/game rental store.

I need to be able to record the hire and return of multiple items.

I have:

tblInventory
EntryID
ItemID
Title
Type
InStock - Check Box

tblHireDetails
HireID
ItemID
Title
HireFee
Returned - Check Box

Focusing on just one item,

If the item is in stock, "InStock" must be true, if the item is on hire, "InStock" must be false.
If the item was on hire (InStock = False) but has been returned (Returned = True) I need "InStock" to return to true.
But, it must be dependent on the HireID and ItemID, so as the same item can be hired and returned many times with many cases of "Returned" without effecting the "InStock" field. As in, Item "1" may have been returned 10 times, but it is currently on hire so isn't "InStock".

How would I work this?

I have an inventory form which is used to add new items to the inventory with "InStock" defaulted to true.
I have a hire form and subform which when an ItemID is input needs to change "InStock" to false.
I also have a return form and subform which is used to return each item individually (Where two items are due back in one day, and another not for 7 days so you can return two without it saying all three are returned) which simply has the "Returned" check box alongside the information displayed in the hire form and subform. This in turn must change "InStock" to true again.

Thanks in ... Read more

Answer:Access 2010 - Update field if..

I use VBA code to control the status and quantities of stock items based on the input and output of the item.
 

1 more replies
Relevance 73.39%

Access 2007 Update Query For Updating A Date Field

Hi I have been asked to write a query for an Access 2007 db

I need to update a date field in a table based on another date field in the same table

1st date field is the person's start date (StartDate)
2nd date field is the person's project date (ProjectDate)
Without going into too much detail, the Project Start date always begins on the 1st of the month

If the person's StartDate is between the 1st - 14th the project start date is counted as on the first day of the StartDate month/year

If the person's StartDate is between the 15th - end of month the project start date is counted on the 1st day of the NEXT month

Example:
StartDate = 7/1/2011
ProjectDate = 7/1/2011

StartDate = 7/14/2011
ProjectDate = 7/1/2011

StartDate = 7/15/2011
ProjectDate = 8/1/2011

StartDate = 7/30/2011
ProjectDate = 8/1/2011

I would like to update the ProjectDate in an update query

Any suggestions? Thanks
 

Answer:Access 2007 Update Query For Updating A Date Field

Create a Column heading like this
numdays: format([StartDate], "dd")
or
numdays: datepart("dd", [StartDate])
this will give you the day of the month on it's own
add a second Column
NewProjdate: IIf([numdays]>14,Format([StartDate],"mm")+1 & "/01/" & Format([StartDate],"yyyy"),Format([StartDate],"mm") & "/01/" & Format([StartDate],"yyyy"))

This will give you the date to use in the update query.
 

3 more replies
Relevance 72.98%

Hi,

Help, I am a novice at Access 2010 and am attempting to write a custom [COLOR=green !important][COLOR=green !important]database[/COLOR][/COLOR] from scratch.
How do I get data from multiple text fields to show up combined in a seperate text field. I want to combine the First, Middle Initial, and Last Name fields so they will be combined in a seperate field I named Complete Name.

I typed the following into the Validation Rule of the Property section for the Complete Name text field however, the Complete Name text field remains blank in Form as well as Table.

Trim$([FirstName] & " " & [MI] & " " & [LastName])

I hope I explained this correctly as I am relatively new to Access. Can you give me any advice or suggestions? Thank You

VMM101
 

Answer:MS Access 2010 Text Field Question

There are 3 ways to do this.
1. This method uses a query to combine the data, but it will not store the combined data and it will not refresh on it's own if you add or change the data.
2. This method combines the data in and Unbound field by making it the Control Source of the Field. This will not save the data to a table but will automatically update.
3. This method uses VBA in the After Update Event Procedures of the 3 fields and will automatically update the data and also save it to a table if required.
The choice is yours.
 

1 more replies
Relevance 72.98%

Hi,

I have a table which is quite large, and is updated every week, so far no issues, but this week I made a mistake when entering the week number and had to delete all the entries from the database , this was a endless task as filtering the week took some good time... question is:

Is there any way that I can "update" a field on the table with the new field, all of them are teh same value, in this particular case is a week number.

Appreciate the help.

Thanks in advance,
 

Answer:Access 2010 - Update field in a table

7 more replies
Relevance 72.57%

I would like for my Access 2007 database to automatically record the date/time when the data in a SPECIFIC field is modified (not when any data in the record changes).

Here is a simplified explanation of my scenario. This database keeps track of cases in my office. A data entry form named "Cases" feeds a table named "Cases." The form includes text boxes that correspond to the fields in the table. Three of these are: "Case_Notes," "Case_Status," and "Date_Status_Change."

I would like for the "Date_Status_Change" field to automatically reflect the current date/time whenever the data in the "Case_Status" field changes. I do not want the date/time to change when information in the "Case_Notes" field is modified.

What code would accomplish this?

The following code will enter the date/time for a change anywhere in the record:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me![Date_Status_Change].Value = Now()
End Sub

But I want to limit it so that it dates changes ONLY in the ?Case_Status? field.
 

More replies
Relevance 72.16%

Hi, Can someone please help. I have looked everywhere but cannot find any info. I am a newbie.I have 1 table and 3 fields, called Trainer, Jockey and Position. I am trying to get a query to count all positions < 4 for each trainer/jockey combination as well as give the total so that i can create a ratio of total positions
 

More replies
Relevance 72.16%

Hi,
Can someone explain how you can put tabs in a text that you are putting in a memo field? I would like to align the text in different columns so it appears that way in a merged document.
Help is much appreciated,
Filip
 

Answer:Access 2010: putting tabs in memo field

16 more replies
Relevance 72.16%

Now in the form, only Names is updated in table tblOrders. I want to update BizUnit in tblOrders too, what should I do?
 

Answer:[ACCESS 2010]2 tables with same 2 fields but only one field updated.

Why do you want to add the BizUnit to the Orders table?
I notice that you also have emails in there as well, they also should not be in there.
Only data relating to the Order should be in the orders table.
The data is then brought together using a Query for Output purposes like Reports & Invoices etc.
 

1 more replies
Relevance 71.34%

I will like to know how to add the working days of the week (5 days) to a date in excel. Also, how can holidays be factored in so that excel will not see it as a working day.Will these require a formula or formulas? Any help will be appreciated, thanks.

Answer:How to add working days to a date in excel

A little tip about posting in a forum such as this one:We can only respond to what you put in a post. We can't read you mind or see your spreadsheet.In your first post you said you wanted to add days to a date.Then is your latest post you used A1 = Friday and A1 = Monday.Which is it? Do your cells contain dates or days of the week?You also said: I want to be use B1 to display what the next 4 working days will beThat sounds like you want B1 to display 4 days in one cell.Let's assume what you really want is to know is the date of 4 working days after a given date. I already answered that question.Did you look at the WORKDAY function in Excel Help as I suggested?If A1 = 6/25/2010 (a Friday) then =WORKDAY(A1, 4) will return 7/1/2010 (a Thursday) because it will skip Saturday and Sunday.If A1 = 6/28/2010 (a Monday) then =WORKDAY(A1, 4) will return 7/2/2010 (a Friday) because there is no Saturday or Sunday within 4 days after Monday.If you really want to return the day of the week, they use:=TEXT(WORKDAY(A1,4),"dddd")If A1 = 6/28/2010 (a Monday) this will return Friday.

7 more replies
Relevance 71.34%

I have a report that the page footer is text box
I want the report footer to say

Loan items listed were returned to FSSCS on Monday October 15, 2012 or whatever date the report is generated for.

="Loan items Listed were returned To FSSCS on " & [DATERETURN_FLD]

This was working before the compact and repair failure and I can't seem to get it back to working.

I keep getting a #name error

Any help appreciated.
 

Answer:Access 2010 Page Footer TextBox with a Field inside

7 more replies
Relevance 70.93%

So I got this T420s from the clearance sale.Arrived on 14th, March and on 15th March the touchpad started acting up.I restored the system to factory setting since there was not much data anyways. Thought that software updates might have messed it up. But no. It was acting up the same way from the word go. It is like as if Right click button of the touchpad is always pushed down. Though on surface it looks normal. They have arranged a pickup of the laptop. There is no loaner scheme in effect right now in Australia it seems. Kind of sad. Never expected this flimisiness from a T series Thinkpad. Infact the Optical Disk drive on the right hand side of laptop, that also has quite a bit of free play. Hoping that they fix the laptop as soon as possible and return it.I will keep forum member informed. Is this touchpad issue a common thing? Since as soon as they heard it they arranged for a pickup. No troubleshooting.













Solved!

Go to Solution.

Answer:T420s Touchpad fails within 2 days of purchase. EDIT: Today it is working ! And failed again. Sad

Sorry for back to back posts.Just out of curosity booted it again and it is working as of now. Loose wiring?I will put it to some regular heavy duty work I do as part of  my project and update the thread again after few hours.Planning to put Adobe Reader 10, Firefox, VMWare, Remove Norton and activate Microsoft Security Essentials and Update Java.If it works out I might call off the pickup. I really need this machine for my project work.

4 more replies
Relevance 70.93%

So I have a cell with a date 10/24/13 and I need to add 15 working days to that date to determine a deadline how do I do that?

Answer:I need to add 15 working days to a date an excel sheet

Use the WORKDAY() function:With your Date in cell A1, try:=WORKDAY(A1,15,)See how that works.EDIT ADDED:This function is part of the Analysis ToolPak add-inif you get an error message, make sure you have thetoolpak loaded.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

3 more replies
Relevance 70.52%

Hello,

Had another thread and couldnt find a solution to my problem. However in the course of working through potential solutions I discovered the larger problem. The Date() function stopped working in my access 2003 database. It was working before (which means the refrences are good) but now if I use it in a form field (in some fashion) i return an error, if i set it as a default value in a table the table errors out when I attempt to save....etc.

Did research for a day on the net and found this problem wasn't a figment of my imagination or unique to me. I found posts on help forums dating back to 2004 describing how "Date() just stopped working...", but in every case the response was always "Check Your Refrences" which never actually solved the posters issues (see my earlier statment about the refrences) and then you get the crickets.

P.S. While researching the issue I ran accross a string of posts where several folks tryied to help someone on the TGS forums with a similar issue. In both cases the Now() function continues to work but Date() doesnt. In that case it was solved because the user could accept the functionality of Now() as it records the time in the table cell.

So I'm hoping for better luck. Anyone run accross this or a solution on how to restore the functionality of the Date() function? This has been really frustrating.

Specifically I am trying to use it as a defualt value in a form for my database. The Now() function works jus... Read more

Answer:Solved: Date () Stopped working?!? In Access 2003

So I solved my own problem here. So if anyone runs into this themselves or if your just plain curious here is the problem.

It has to do with a versioning issue and installations of Access 2003. So if you create access 2003 on Machine A and then move it via whateve method to Machine B you can run into problems. If Machine A has any slightly different installation issues with the Refrences your hosed. Now why this only effects some code commands like Date() and Term() but not Now() you will have to find someone a whole lot somarter than me.

The simple solution is simply open up refrences and deselect any refrence that has the tag "MISSING" in front of it. Once this is done the missing refrences will not cause these code commands to error out and Date() or Term() should work fine.

P.S. Oh and once this issue was solved the Default Value in properties solved my previous issue with Data Entry causeing Date() to stop working. The versioning on access and the refrences can cause a cascading failure so the problem gets worse and worse over time. Case in point Date () works in all but a certain instance and moves to a state where it doesnt work in any instance.
 

1 more replies
Relevance 70.52%

I have a form that will be used to preview reports with the given combo box criteria. Everything was working perfectly with the first three combo boxes I made. The fourth one (which is blank in 264 of the 311 records) made the report only return 47 of the 311 records when I searched for all records (keeping all combo boxes blank). Is there a way to ensure that the total number of records are used in the filter? I hope my question is clear. (p.s. my OS is Windows XP) Thank you!
 

Answer:Access 2010: Combo Box Field Limiting Full Records of Report

7 more replies
Relevance 70.52%

I have a form that I'm using to enter orders for inventory. I just ordered 2 phones I'd like to have listed as 2 separate records (they will be physically tagged individually, so they need to be separated in the table).

I'd like to have a Quantity field that when I type a number and save the record from the form, the number in the Quantity field adds that number of identical records (aside from the Autonumber field for the primary key).

Is this possible? If not, how should I go about it?

We have larger orders, sometimes 10-20 items, all with the same everything in the record except for the physical tag number that will be applied to each item when it comes in.

Thanks in advance.
 

Answer:Access 2010: Form quantity field that adds records to table

If the form is for entering the actual order details then you shouldn't have a record in the order table for each item in the quantity field.
What you could do is is to add a record for each item in the quantity field in the Inventory table using the Item's ID to identify it.
 

1 more replies
Relevance 70.52%

This is for the database:
I am trying to use the tct codes in a combo field on the Form and have a different field backcolor for each one. So I missing something just not sure what. Thanks Alan
 

More replies
Relevance 70.52%

I use due dates on my Outlook tasks. When I customize my Tasks list (in the Calendar view) to group by due date, I just get too many groupings (one for each due date!). I would like to know if I can create a custom field called 'Due Month' and/or 'Due Week', extracted from the 'Due Date', so I can view my tasks by what is due during a given week or month. If the answer is yes, any suggestions on the technique I would use would be greatly appreciated.
Thanks in advance,
Mac
 

More replies
Relevance 69.7%

I need a date field changed into a text field of YYYYMMDD so for example the date of birth field is 3/17/1953 and I need that converted to 19530317 in the Excel spreadsheet. Thanks.

Answer:I need a date field changed into a text field of YYYYMMDD

under the format options.

5 more replies
Relevance 69.7%

the answer to this might of been found with a search but hey i'm lazy today and wanted to see who the excel masters are

using excel 2007, cell a1 contains an array formula (CSE) like so - {=MAX(LEN(A2:A470000))}
cells a2 through a470000 contain dates such as
12/12/2011
etc, you get the idea, i need the formula or something else entirely to return 10 not 5

thanks in advance
 

Answer:Solved: excel length of date field

i'll answer if myself thanks to another web site, Mike these guys are slackin' 'round here it looks like

=MAX(LEN(TEXT(A2:A470000,"m/d/yyyy")))

exit celll with ctrl, shift, enter
 

1 more replies
Relevance 69.7%

I receive periodic Word (.rtf) documents from a property rental agency whenever my property is rented. The document contains the dates rented, the renter's name, etc. There is a "date field" in each document which automatically shows today's date, rather than the date the doc was created and sent to me. If I want to forward the doc to the renter a week later how do I stop the date from updating when I send it and stop it from updating when the renter opens the file?
 

Answer:Solved: Stop Updating the Date Field

6 more replies
Relevance 69.29%

Hey everyone,

I'm having problems with Access, where I have a date/time field, which stores a month and a year (successfully), but still automatically assigns the first date of the month in question.

I used the following input mask (00/0000;;_) for the date field, and so far I haven't had any troubles with putting in a month and year. So far so good. But when I complete the date and move on, Access changes it to a full date. e.g. 06/2010 --> 01/06/2010.

Now, I'm actually fine with this part, my only problem is that I don't want it to do this in forms (both datasheet and normal view) or reports.

I heard something about using a Format (http://office.microsoft.com/en-us/access/HP010990151033.aspx) to adjust the display, but I can't seem to get that to work. Is there any other way to tell Access how to display the date field, like what can be done in Excel?

This is rather urgent as I have a project due tomorrow and this problem (as well as others) have now been reached and I'd like to solve them as soon as possible.

Any help is appreciated, and I look forward to your responses.
 

Answer:Solved: Urgent: How do you format the DISPLAY of a date field?

8 more replies
Relevance 68.47%

Hi

I am having issues with an Access 2010 db hanging when I try to open/edit/save etc on a report in the db. The db is pretty basic and small, while the report I am working on has no code in it and runs off a simple query. The db was created by an experienced Access developer.

All tables and queries run in a normal way and results appear on screen pretty much instantly. The report is based on one of the queries.

The report works fine for all other users from what we can tell and only I have the issue on my machine.

I am running Windows 7, MS Office 2010 32-bit with SP1 installed. This is exactly the same set up as the developer who created the db. The developer has had me run a few diagnostics and tweak some settings in Trust Centre and so far nothing has worked. My IT dept seem a bit stumped as this is not a bit of software they like and as such offer almost no support.

I would be grateful if anyone has any ideas at all how I can solve this.

Thanks
 

Answer:Solved: Access 2010 hangs only when working on reports

Do you have any other reports that do work OK?
This appears to be a problem with just your machine so it suggests a problem with your Access/Office, have you tried "Repairing" Access or Office or getting IT to repair it?
Did you create the Report?
If not try and Create a new version the same as it yourself in your database. Dont' worry about any kind of formatting, Grouping or anything else, just try and get the Report to run based on the same query as the original.
 

1 more replies
Relevance 68.47%

Hi All,

I have two text boxes on a form. I want to add a date into txtBoxA and I need code to automatically make the value in txtBoxB to be 3 days less than txtBoxA.

The tricky part is that I do not want to count weekends.

So for example if I have Thursday 11th March as "A","B" should be Monday 8th March.

Whereas If I Have Weds 10th March as "A", "B" should be Friday 5th March and NOT SUNDAY.

Is there a way to do this in VBA code?

Thanks
Simon
 

Answer:Solved: Take 3 days off of a date

7 more replies
Relevance 67.65%

Hi guys,

This is my first post, but I've checked around and this community looks like it knows its stuff so I hope you can help me .

I'm currently developing a equipment management database. I have a table storing the different pieces of equipment and another table that records when pieces of equipment are down for maintenance.

Now I need to have reports come out that specify each equipments availability for the week, month, and year to date. I believe doing this with queries may bog down the database in the coming months or maybe a year or so down the road. So what I would like is to create another table that records daily availability.

The basic things that I am unsure how to do yet but I believe I can figure it out on my own is having this table update automatically the previous day's records with each machines availability in a % form, (ex: truck down for 12 hours means 50% availability).

The part where I am unsure how to proceed is getting this availability recorded and keeping it current. I can't have the Availability table restricted to looking at one record in the Down table because an item may go down twice or more in one day generating two or more records in the Down table. Also it is often that the maintenance may not be recorded properly and need altering at a later date or that it wasn't recorded at all and a new Down record must be created. This would need to effect the corresponding date in the Availability table for that piece of... Read more

Answer:Access 2010 Date Tracking

16 more replies
Relevance 67.65%

I have constructed a database for membership of a small club. I would like to know how to calculate the type of membership for members based on the number of years they have been in the club. ie joined in 1996 and therefore have been members for 16 years this makes them a full member and will cost £45. How can I do this ?

Help please

mepsele
 

Answer:Date calculation in Access 2010

Welcome to TSG mepsele, hope you find some answers and take the time to help others when you get a chance. Thats how the communinty works.

This expression will give you the date difference between Now and a date field (I used ED_Date) in years.
Expr1: DateDiff("yyyy",[ED_Date],Now())
 

1 more replies
Relevance 67.65%

I need to put a date on a label that is generated from a report in an Access 2010 database. When the user runs the label report they need to be prompted to enter a date so it will print on the label.
 

Answer:Access 2010 Calculated Date

6 more replies
Relevance 67.65%

Hello,

1st Question:

I'm currently building a Database for my job. I have a field titled "Date Received." What I want to have access do is automatically populate a "Due Date" for 2 business days after the "Date Received" field.


2nd Question:

In my database there is a "Status" field where the agents will be able to select a status from a drop down menu. Is there a way to keep record (auto Date/Time stamp) every time the status is changed? How do I do this?

I have this in a Form. I'm fairly new to ACCESS but am a quick learner. Please help me with this!!!!

Thank you for any help! Just joined this site today so I hope I can find the help I so need!
 

Answer:MS-ACCESS 2010 - Date Stamps

I need help! Some one please help me with this!
 

16 more replies
Relevance 67.24%

Hi! Does anyone know what is the format of the date in the field "(battery) Manufacture Date" informed by Lenovo Vantage (Hardware Configuration > Energy)? It seems not to follow Windows setting. In my case, it is set to Brazilian Portuguese DD/MM/YYYY, but there is not a leading "0" in the returned value (see attached image). Does it really disregard the Windows setting and presents it in a fixed format (maybe American English)? When was my battery manufactured (Oct. 07 or July 10)?Thanks in advance.  

More replies
Relevance 66.83%

Hi,

I am working with a database that is added to every day and I need to be able to pull out those records that apply to an overnight shift. The start time is given as dd/mm/yyyy hh:mm:ss and I want to be able to select all the records for shifts that started between 19:00 on the day before and 06:00 of the morning I run the query.

As this needs to be done everyday I was hoping that a query could use the criteria of Date()-1 and Date() along with the times to select those records, so I have tried this (and a few variations of this) but nothing has worked so far

SELECT Table1
WHERE StartTime BETWEEN Date()-1 #19:00:00# And Date() #06:00:00#

Can anyone help me with this?

Many thanks!
 

More replies
Relevance 66.83%

I'm still a rookie with MS Access, and I know some of you Access guru's will know the answer to this.

I need a validation rule that will give a popup window warning if the date entered is MORE than 180 days from todays date. Can anyone help?

Thanks
 

Answer:MS Access 2010 date validation rule

Set the after update event handler to compare the date entered meets the criteria.

If Me.Textfield >= Date() +180 Then
Do something
Else
continue
End If
 

2 more replies
Relevance 66.83%

Hi,

Want a macro to sort data from (Column B,C,D,E) with whatever date mentioned in Cell A2.
Column B,C,D,E have different dates what I want is to sort those columns as per the date mentioned in cell A2.
Suppose if in cell A2 the date is 11/26/2009 then it should sort the data from the Column B,C,D,E with that particular date in a new sheet in that workbook.
And would like to assign that particular macro to a button

Please help
 

Answer:Solved: Sort today date with Macro in excel

16 more replies
Relevance 66.42%

date/time format using d hh:mm How to suppress 0 days?

Answer:Excel date/time format as days hh:mm and suppress 0 days

As far as I can tell, there is no way to suppress the Zero using that Custom Format.You could probably process the data and remove the Zero but you will need either a formula or VBA.Is this the result of a calculation?MIKEhttp://www.skeptic.com/

2 more replies
Relevance 66.42%

Hello,

I have a large report of application forms which have been submitted including the date when they were submitted however the dates are formatted incorrectly.

1. Although the cell formatting is dd/mm/yyyy the data is being shown in mm/dd/yyyy which means the dates are incorrect (e.g. 2nd January would read as 1st February.)
2. As a result of this dates which are passed the 12th of a month are not accepted as dates (it reads the day figure as months and there is no 13th month etc)

I have tried to use the text to columns function but that makes no difference and I have tried to extract just the dates but I've had no luck.

Can anyone help, I've attached a small sample of the data.

Thanks

Daniel
 

Answer:Exported date field not recognized as date in Excel

Where does the data come from?
What do you get if you have no cell format?
 

3 more replies
Relevance 66.42%

I accidentally formatted the date cell & it only accept numbers

Answer:how to correct a date field when you enter a date a number a

I don't know what you mean by "it only accepts numbers".Why can't you just format it as a date again?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 66.01%

I was recently hired to work in a recreation department on a special event that happens every year. The Rec Department uses an Access database. I have no idea how old this database is. My boss has been working here for 10 years and she said the database was in place when she was hired.

One of the things my boss wants me to do is to add the date on the page footer of all the reports. I have tried using the wizard on the ribbon, using the expression builder, and just typing in =Date() into the control box. Any way I try it, I still get an #Name? error when I run the report. I was hired because even though I think of myself as a complete beginner when it comes to Access, apparently, I know more than most Admins about this program. That being said, I am an Administrative Assistant and not a programmer, nor do I have programming experience, so please keep that in mind when you answer this.

I checked in the VB editor under tools - references and it seems this organization is missing the utilities.mda file. I tried a search but it was not found. Could this be a reason for the error? If it is the reason, what would be a work-around?
 

Answer:Access 2010 Report #Name? error in =Date() control box

6 more replies
Relevance 66.01%

I purchased the computer on SL510 on April 14th.  When I started the CRT screen donot come up. When I contacted supprort I was told that system board need replacement. I have been contacting support then customer service etc, till today no resolution. Now I am asking for refund as I had enough. I am waiting impatiently for the last 4 days as I am told this is waiting for financial approval. I wonder, how this was not even switched on and some basic tests not conducted before the dispatch.  How to escalate to dispute section. This unit was purchased in Australia.  Please suggest to whom sholud I approach for this region.

Answer:I purchased SL510 on April 14th. Not working till today (17/05/2010). CRT is blank

You might want to consider going to an IT to solve your problem, or contacting another country for support.





------------------------------------------------------------------------------------------Lenovo ThinkPad SL410 2842 (Intel Core2 Duo T6670, 2 GB DDR3 RAM, 250 GB 5400 rpm Hard Drive, DVD-RW drive, Intel WiFi Link 5100, Intel GMA 4500MHD, Win7 Home Premium 64-bit) http://nugenivy.blogspot.com/

2 more replies
Relevance 65.19%

Help! This should be easy but I can't get it to work.

I have a report that must group by Trip# and sort by ascending activity date all of the various activities that took place on multiple days of the trip.
Further, the trip with the earliest date must come first regardless of the Trip#.
I've accomplished getting the earliest starting date trip first and grouping its detail with a unique string made up of the earliest date of each trip formatted to YYYYMMDD+Trip#. This prints a trip total and works fine.

But the daily activity for the trip is out of date sequence.
Adding a Date field to the 'Sorting and Grouping' insists on breaking on and printing a sub-total for each of the trips activity dates.
Without the sorting & grouping by date the trip records are out of sequence even though the data source query is sorted ascending by activity date.

(see attachments that show the above)

The only solution I can think of is to create a sub-report sorted by activity date.

Does anyone have any better ideas?

Jim Shannon
JES Computer Systems
Access Developer.
 

Answer:How sort by 2nd field (date) without grouping when date changes?

7 more replies
Relevance 64.78%

After running a large update of windows on May 18 I found that ALL my files in ALL my folders are showing a Date Modified of May 18 2013. Can't find a similar report of this problem. Any ideas?
Using Windows 7 Professional 64-bit

Answer:File Date Modified reset to today's date after windows Udate

Would this help? enter link description here

1 more replies
Relevance 64.78%

There is hopefully a very simple answer to this, but I'm not used to working with bound forms.

I have a bound form with four cascading combo boxes. All four fields are required (this is a lossless join form). It's a very lightweight form with native navigation and selectors. The only VBA so far is to run the requeries.

Anyway, I want to clear the values from lower cascading combo boxes if the value of a higher combo is changed. The table constraints don't allow nulls or zero-length strings, so every time I try to set a combo's value to null or nothing, I get a standard Access error for a field constraint violation.

My confusion is, the form is dirty, and the field values are being written to a new record, but the record itself shouldn't have posted yet (i.e. the commit shouldn't have happened). So why can't I blank out a field? What's the proper way to do this?

In my mind it seems like I would clear the record entirely and then rewrite the combo values that I don't want to delete. Is there a better way?

chris.
 

More replies
Relevance 64.78%

I am also new to Access 2010 so am slowly learning my way around. I need an iif statement that will allow me to create a new column telling me whether one date field (Course Date) is within or outside the range of two other date fields (Previous Effective Date) and (Previous Expiration). I hope that someone can help me solve this issue quickly. I've tried various ways to write the statement and none of them worked. One statement only returned the information as all being outside of the range, which I could clearly see was not true. Here are some examples of what I tried. None of these worked at all. Thanks. Joyce

=IIf(([course date]>=[Previous Effective Date] and <=[Previous Expiration Date]),"between dates",IIf([course date]<=[Previous Effective Date] and >[Previous Expiration Date]),"outside date range")

In/Out Range: IIf(([course date]>=[Previous Effective Date]),"between dates", And IIf([course date]>=[Previous Expiration Date],"outside date range "))

In/Out of Range: IIf(([course date]>=[Previous Effective Date] And <=[Previous Expiration Date], "between dates") And IIf([course date]<=[Previous Expiration Date] And >=[Previous Expiration Date], "outside date range"))
 

More replies
Relevance 64.78%

I need to return a number associated with a date based on today's date. For example: A1:A24 are numbers 1 to 24 and B1:B24 contain the 15th and 30th for each month of the year; 15-Jan, 30-Jan, 15-Feb, 28-Feb, ... 30-Dec.Cell C1 needs to return the number from A1:A24 based upon today's date compared to the date in B1:B24.For example today is 08/21/15, what number should be returned in Cell C1. It should be 15 as 08/21 is less than 30-Aug.message edited by jmichels

Answer:Return a number based on today's date compared another date.

Try this formula, see how it work for you:=INDEX(A1:B24,MATCH(TODAY(),B1:B24,1),1)This will work for most dates, but errors out if the Date is 01/14/15 or less.MIKEhttp://www.skeptic.com/message edited by mmcconaghy

4 more replies
Relevance 64.37%

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?
 

Answer:Solved: Access - Convert Date Time to Date

10 more replies
Relevance 63.14%

Hello!
When dealing with dates in Access 97, is there a relatively simple way to exclude weekends?

This would be very useful for me to know for a variety of queries and reports, but at the moment all I need to do is calculate a target date, which should be a starting date plus 6 working days.
The starting date is called the "In Program" date

Obviously, working with calendar days is a simple process - the control would just be:

=[In Program]+6

But as we don't (currently) work weekends, Saturday and Sunday should not be included in this 6 day period.

Any help?

Gram
 

Answer:Access 97 - Working days calculations?

10 more replies
Relevance 62.73%

In excel I am trying to compare a column of various dates against the current date (reported in one cell using the =today() function) and if the dates in the column is past today's date it will report "Late" in the adjacent row or if the date in the column is the same as the current date it will report "Due" in the adjacent row. "If/then" doesnt seem to work. I have thousands of columns of dates but simplied exampleCurrent Date = 04/21/2014Column A, Column B, Due Date, Status, 04/12/2014, Late04/21/2014, Due

Answer:Comparing a date in column against today's date

First, a posting tip...If you click on the blue line at the bottom of this post, you can read the instructions on how to post data in this forum so that the columns line up and your data is easier for us to read. It may take some trial and error via the Preview screen, as explained in the instructions, but it can be done. Second, you told us what you want if the date to be checked has past or is the same as TODAY(), but you didn't tell us what you want if the date is later than TODAY().I assumed you want a blank cell.

A B
1 =TODAY()
2 Due Date Status
3 4/12/2014 =IF(A3<$A$1,"Late",IF(A3=$A$1,"Due",""))
4 4/21/2014 =IF(A4<$A$1,"Late",IF(A4=$A$1,"Due","")) Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

3 more replies
Relevance 61.91%

I have a table linked to a form which is "=now()" and I do not the end user to be able to change this field however I cannot apply user/group permissions

Any Ideas????
Please
STUART
 

Answer:Solved: Access NO editing in a certain field

Please can somebody help me
 

3 more replies
Relevance 61.91%

Im having trouble trying to get my search box to just display the data i want!
I have a table called TblInfoQubeRef Which has 2 columns called SiteName & QubeRef. (Populated to use a combo)
I have another table which i have many fields - to hold info about telephone lines for the SiteName. I May have many lines for the one property.

I created a QryDataMainLineSearch with certain fileds in of data i want displayed.

My end result is, i want a form with a combo box displaying the site names - when i select the SiteName it populates the data below of all phone line for the SiteName.
Can any body help - OBP?
 

Answer:Solved: Access Search Field

11 more replies
Relevance 61.91%

I am trying to add an attachment field to an existing table in ms access 2010. When I try to add it I get a popup that states "operation not supported for this type of object". Does anyone know how to make this work? I am not an Access expert by no means.
 

Answer:Solved: MS Access Attachment Field

6 more replies
Relevance 61.91%

My calculated field populates a combo box. From it's selction, how can I display the ID of the selected value in a textbox? Thanks.
 

Answer:Solved: Access: ID from calculated field

10 more replies
Relevance 61.91%

Long time no see, took a bit of a break... time sure fly by huh?

My Query's Y/N collum result in -1/0 when I run the query. I want it to be Yes/No.
Is there a ... better/quicker way to do this other than using IIF() function in the sql?

I use this field in a combobox on a form, interesting thing is some of the combo's display Yes/No whilst some display it as -1/0

Thx
 

Answer:Solved: Access Query Y/N Field

7 more replies
Relevance 61.91%

Hello,

I researched this issue on the net, but couldn't find any solution that I could actually make work, so I am asking for your advice here..

I have lots of input data that I need to combine and process in Access. The issue is that the input data is mostly in table format, showing data by week, month, quarter and year-to-date,like how it is shown below.
Location Metric wk1 wk2 ....wk52 Jan Feb .... Dec Q1 Q2 Q3 Q4 YTD
The data in this format of course, violates first normal form, but this is the form the data are available in, from multiple data sources..

As stated above, I need to combine data into one table, so I think I need to create a couple for cycles i=1 to 52 (for weeks), j=1 to 12 (for months), etc. to be able to combine the numbers into one big table. Within the cycle, I need to update data for each metric, for each week, month, ...

So, the question is, how can I reference variable values as field names? Based on my research, this is how far I could get, but of course, it doesn't work:

Dim rsOntime, rsData As Recordset
Dim MVar As String

Set rsData = ["Data"] ' Table to combine all data into
Set rsOntime = ["Ontime"] ' One of the input tables
i = 1
j = 1
k = 1

' Loop for metrics
For k = 1 To 24

' Loop for weeks 1 to 52
For i = 1 To 52

If i < 10 Then iText = "0" & i
If i >= 10 Then iText = i

MVar = rsOntime.Fields(iText)
rsData(MVar) = rsOntime!InputData

.RunSQL ("Update Data IN... Read more

Answer:Solved: Access - use variable value as field name

6 more replies
Relevance 61.91%

Hi

I hope somebody can help.

I am attempting to set-up a spreadsheet to workout calculations based on working days (not Saturday & Sunday) only.

I can work it out using Saturday & Sunday, how do I exclude weekends from the calculation?

Thanks

Dave
 

Answer:Solved: Excel 2010 - date Calculations

use networkdays()
or
workdays()

see
http://office.microsoft.com/en-gb/excel-help/networkdays-HP005209190.aspx

http://office.microsoft.com/en-gb/excel-help/workday-HP005209339.aspx
 

3 more replies
Relevance 61.91%

Hi,
I'm using Microsoft Word 2010
Instead of typing in "today's date" every time.....
How do I modify the date macro I made for Excel so it will work in Word.
And where do i put it ?

Then I can have an Icon on the Ribbon !!
HTML:
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.NumberFormat = "mmmm/dd/yyyy"
Selection.Value = Selection.Value
mike
 

Answer:Solved: Macro for date in Word 2010

6 more replies
Relevance 61.5%

I did a quick search and was not able to find anything that I was looking for...

Here is what I have..

I am using Access 2003 and I have a table that tracks file names. I am looking to do a Find & Replace on anything before the "/".

For example: Find and replace you can use "*/" and replace with "". That will remove anything before the "/".

Ex: /My Documents/Downloads/Files/testing.xls
/Documents And Settings/Users/Desktop/TestFile/ReadOnly/testing.doc

So what I want to end up with is just:

testing.xls
testing.doc

I have tried Replace(), that only replaces the text and will not remove it, so I am not sure where to begin on this one.

Thank you in advance!

Edit:

The closest thing I have come up with is:
RTrim(Right([F1],InStr(2,[F1] & "-","-",1)-1))

But that does not stop at the "/" and sometimes it removes part of the file name...
 

Answer:Solved: Access - Removing Data in Field

9 more replies
Relevance 61.5%

Hi
Can someone help me to put a dash in the middle of 8 numbers on a number of records in a field in Access.
eg change 12345678 into 1234-5678 (each record in the field contains a unique number)

Thanks
 

Answer:Solved: Access Manipulate data in a field

7 more replies
Relevance 61.5%

Hi all,

I'm trying to create a report based on a small database of info imported from Excel. What I want to do is have the report totalled by month. I created a new field for the main table with an update query that added just the month name (don't know if I need that to total by month or not, there is a date field as well). I've uploaded the database to give a better example.

What I want to end up with is a report that shows a Monthly total for each Source in the database. There are five sources, so this should be 60 different totals. There is a field in the database called 'galTKN', this value should appear in that summary as well. The value will not change and is the same for every record. I was debating creating an extra table for this, but it could change in the future.

Let me know if you need any other information. TIA.
 

Answer:Solved: Access: group by field in a report

6 more replies
Relevance 61.5%

I have an access main form that contains a subform. The subform is setup to display in datasheet view.

On the subform there is a memo type field. Is there a way to have the memo field to word wrap?
 

Answer:Solved: Access Subform - Memo Field

I don't think you can in Datasheet view. You could try setting the Can Grow property to yes.
 

2 more replies
Relevance 61.5%

Hi all,

I need some more help with a form in Access. I used the wizard and created a form with all fields I thought I wanted. Now I want to add another field (item from the field list) from a table in my database. I cannot find a way to update or change the field list of my form once I have created it.

It is the same form I had help with last time (an overtime tracker). I select an employee from the list who has his salary info saved in the employee table. When I select the employee in my form, I want to display his salary in another text box so I can use it for calculations.

On a similar note. I updated the table that my form is based off of and renamed a column. Whenever I open the field list in my form, the same column heading remains even though it exists nowhere else in the database. How do I update the field list?

Any help would be appreciated. If you need more info please let me know, thanks.
Ronyace
 

Answer:Solved: Adding field to Access Form

10 more replies
Relevance 61.5%

Update a time field
I have a Table with 2 fields. The 1st field is Labeled "TimeStopped". The 2nd field is Labeled "TimeDiff". I need to subtract the next record of TimeStopped field from the preceeding record TimeStopped field and put the difference in field 2 TimeDiff so I can know how long each stop was.
Any help in using Vba to do this.
Thanx,
john
 

Answer:Solved: Update a time field with access vba

16 more replies
Relevance 61.5%

Hi,

I have two tables and i want to lookup a value of one from the other.

The first table contains a field called Department.
The other field has Department ID and Description.

I want to display the descriptions in the first tables Department field.

I've tried the lookup wizard data type in design mode, but it just returns the Department ID's instead of the descriptions.

EDIT

problem was my logic. no worries.
Any help appreciated.
Lewis
 

Answer:Solved: Access 2007 Field Lookup

Lewis, it should only show the ID, as that is what it should store. Seeing the Description is what Forms are for, users should not really interact directly with tables.
 

1 more replies
Relevance 61.5%

Of all of the years I've worked with Access, I've never needed to do this; well here I am being asked to get a list of field names from a table. I'm aware of a few ways (set a list box to be field list, and using the db analyzer). I also found some code that doesn't work to read the database fields and dump them into a file (I'll paste to this note - apparantly I'm not running the correct version of ADO to use it).

Any help would be greatly appreciated. It dies on a "User-Defined type not defined" error inside the funtion at "Dim MyDB As New ADOX.Catalog"

I found the code on a developer article, but not sure I'm implementing it correctly. I feel like I'm out of my league on this one, but willing to give it another shot.

Thanks in Advance

Jim
Code:
Option Base 1
Public Sub Enumerate_Table()
On Error GoTo ERROR_PROC
Dim aryFields()
Dim lngCount As Long
lngCount = 0
strInput = InputBox("Please enter the name of the table for which" & vbCrLf & _
"you wish to list FieldNames & Descriptions." & vbCrLf & vbCrLf & _
"Output will be placed in tab-delimited text file.", "Table Name Input", "MainTabl")
If StrPtr(strInput) = 0 Or Len(strInput) = 0 Then
Exit Sub
Else
strSQL = "SELECT * FROM " & strInput
Dim Adofl As ADODB.Field
Dim rs As Ne... Read more

Answer:Solved: Getting Field Names from Tables in MS Access

6 more replies
Relevance 61.5%

Hi Everyone!

I'm working in Access 2003. I have a table (pulled from a Cobol program) that has active user info. The user names (first & last) are in 1 field. In Access, I want to separate the first and last names into 2 separate fields: Fname & Lname. The names are currently displayed like this:

Mouse, Mickey

I want this to be done in a query so that I can export the data into a spreadsheet for employees to use.

The left, right, trim, mid functions don't seem to work for me, unless I don't know the full extent of their capabilities.
Thanks to all who can assist!
 

Answer:Solved: Extract part of a field, Access 03

I have this database from a previous post that does it in a form, which you could use to save the data in a table and then just use a simple select query for your export.
It dosn't use commas as seperator, just spaces but I am sure it wouldn't take you much effort to convert it.
 

2 more replies
Relevance 61.5%

On a form, need assistance in fixing a vb script msgbox problem. I have placed the code listed below to verify that the user wants to change an existing value in a field. The code below works fine, UNLESS the user has made other changes on the form prior to changing the checked fields
There are 2 fields ("NoBill" and "Lname") that are checked for changes. But if someone changes a remarks or other field in the form prior to changing "NoBill", and when the msgbox pops up to verify the change, if the user selects "NO", then the other fields that were changed before are also undone.
Q: how to just undo that particular field, not all the changed entries on the form.
Code:
Public Function Conf()
Dim Msg, Style, Title, Response, MyString

Beep
Msg = "You have altered existing data are you sure you wish to do this?"
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm record change"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
Screen.ActiveForm.Undo
Exit Function
DoCmd.RunCommand acCmdSaveRecord

End If
End Function

and then in the Before Update properties
Code:
Private Sub NoBill_BeforeUpdate(Cancel As Integer)
If Not Me.NewRecord And Me.Dirty Then
Call Conf
End If
End Sub

I tried creating two separate public functions ("Conf_NoBill" and "Conf_Lname"), and calling those particular functions based on those fields' Before Update ... Read more

Answer:Solved: Access 2k field change verification

13 more replies
Relevance 61.5%

Hello. I need help (in more ways than one but I digress ) with an Access interface. How do I "lock" or "dim" fields?

If Frame264.Value = 1 or 3 Then I need these fields locked....

NICUTotal (text box)
NICU Total (label box)

And

OBTotal (text box)
OB Total (label box)

Any help you can give would be greatly appreciated!!
 

Answer:Solved: Help with dimming/locking a field in Access - Please...

8 more replies