Computer Support Forum

Solved: Access 2010 date field validation problem

Question: Solved: Access 2010 date field validation problem

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

Relevance 100%
Preferred Solution: Solved: Access 2010 date field validation problem

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 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 75.98%

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?
 

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

16 more replies
Relevance 100.04%

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 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 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 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 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%

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%

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 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 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 83.23%

... that will validate a car registration field.Allowing for the plethora of styles of car registrations where there are 'old style' registrations, 'new style' registrations, registrations where some letters or numbers may be omitted (for genuine reasons to make a cherished plate), I haven't been able to work out a validation rule for an Access database.Has anyone ever found - or been sad enough to generate - such a rule? I can't work one out.

Answer:I need an Access field validation rule...

Interesting problem which I haven't got an immediate solution to. There are a lot of variables - new style, pre-fix letter, suffix letter, old style and within the old style up to 3 letters and up to 4 numbers before or after the letters. Similar problem would arise within Excel wonder if Vog can help?

10 more replies
Relevance 81.59%

I'm trying to make a simple form but one of the fields needs more complex validation than it allows me to do. Basically, the user chooses a material, enters the number of units and their combined weights. Another field calculates their average weight (total weight/units). I have a table specifying the range that each material's average unit should weight. I'm trying to make access validate the calculated average weight field (or something equivalent) according to the material chosen by the range specified in the other table.

I'd rather not forgo the table with the ranges by hard coding the ranges in VBA so that users could change the ranges.

I've put a relationship for materials from each table and display the range in the form but now I can't enter in a new entry.

I need help.
 

Answer:Access 2007 Complex form field validation

16 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 76.26%

Hi. Here is the situation.

I have two tables (Field_a & Table A and field_b & Table b) and I wanna set up validation rule for field_b (Table b).

Rules needed for field_b:
1. If field_a (Table A) = 0, field_b (Table b) = 0.
2. If field_a (Table A) = 1, field_b (Table b) > 0.

Thanks a lot for the help!
 

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

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%

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%

New to the forums, currently building my third Access database. This one is the first that will actually be used.

I have a Visit table with a DateVS field and a Client table with a EnteredOnCL field. DateVS is date of the visit. EnteredOnCL is a hidden field with default as Date() so I know when the Client was added to our system.

I want to prevent a visit from occurring significantly before the Client was entered, but I also want to give some wiggle room for late data entry. Here was my idea for a validation rule on DateVS:





DatePart("yyyy",[DateVS])>=DatePart("yyyy",[EnteredOnCL]) And DatePart("q",[DateVS])>=DatePart("q",[EnteredOnCL])Click to expand...

I was hoping this would require DateVS to be in a quarter equal to or later than EnteredOnCL. But it looks like I can't refer to a field from another table in a validation rule.

Is there any way to do this? Thanks.
 

Answer:Access 2010: Referring to another table in a validation rule

I need to do this because I'm separating Visits by New and Returning Clients with a nested query using these expressions:





Expr1: IIF(DatePart("q",[DateVS])=DatePart("q",[EnteredOnCL]) AND DatePart("yyyy",[DateVS])=DatePart("yyyy",[EnteredOnCL]), 1, 0)

ReturnClient:IIF(Expr1=0,[ClientID],null)

NewClient:IIF(Expr1=1,[ClientID],null)Click to expand...

If the DateVS is in the same quarter as EnteredOnCL, the Client's visit is classified as new. Different quarters, returning. But this doesn't work if there are visits more than a quarter before the client was entered.
 

3 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,

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.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.57%

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 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%

I've been working on a database to save subscriber & ticket information for my community non-profit theater group. After lots of trial and a ton or error, I like what I have, but determined that I need to change the way I store some of my data. This required the addition of a text field which has a list of 3 lookup values. I added this to the table in design view--no problem.

The problem is in the forms I've already generated. I went to design view for my form and put in a combo box for this new field. The wizard asks me what I want to do with the values: remember them for later or enter them into a field. I would like them entered into my new field. That's what this combo box is for. However, to enter them into a field, the wizard lets me select only from a drop down list of the fields available in that table, and it doesn't include my newly added field. I imagine that this is because the form itself was created before I added this field. I could recreate the whole form, but I spent a long time designing it, and all I want is to add the one new field. If push comes to shove, I'll start from scratch and do the whole form over again, but I can't believe there is not a way to do this.

If it's of any consequence, this form is actually a subform, but I'm attempting all the modifications to the subform on it's own, so I don't imagine that's got anything to do with this prpoblem.

If someone could please educate me I'... Read more

Answer:Solved: Access--new field in table & problem with form

8 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 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 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.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 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 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 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%

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.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.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 67.65%

Hi,
I'm working on an Excel project of mine and wanted to use VBA to create a Data Validation List in a merged cell.
The strange thing is that using VBA for some reason the list does not show as a drop down list but as a long string
The string is the 26 letters of the alphabet
A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z
Click to expand...

Since I was doubting my own VBA code I decided to record a macro and added all the 26 letters of the alphabet
The resulting code:

Code:

Sub DVApply()
' DVApply Macro
Sheets("DataForm").Select
ActiveSheet.Unprotect
Range("B2:C2").Select
ActiveCell.SpecialCells(xlCellTypeSameValidation).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("D5").Select
End Sub

Now here is the thing. The moment I record the macro the list shows up nicely as a dropdown list, but if I run the macro the new list will show up again a a single string and not in a list unless I open the Data Validation Dialog and click Apply and close it.
I&#... Read more

Answer:Solved: Excel (2010) Data Validation using VBA not working as expected

Whatever the problem is I wrote another routine and it works now.
I've thrown every range at it and it (still) works.
I'ts still strange why even the recorded macro does not work when you run it again afterwards.
Let it be, one more of Microsoft's deep secrets
At least this post had 15 views, but I'm marking it solved.
 

1 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%

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.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 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 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.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.42%

Hi everyone,

I wonder if someone can help with the following ? I had an Access '97 database working for a number of years with no difficulty. Now, just to do a cleanup, I formatted a new C drive, installed XP Pro w/SP2, installed Access '03 and copied the database to the new drive. Everything works except for the date() expression in a text box in form view, I can't get it to display the current date. I have tried adding a new box and using expression builder to set it up to display the date, but no joy. When I return to form view from design view, the box shows "#name?". There are no calculations or macros associated with the box, all I want it to do is show the date ! Any help would be much appreciated, thanks, John.
 

Answer:Solved: Access 2003 date() expression problem

14 more replies
Relevance 66.42%

Hi there,

I have a dbase created in the UK and I am storing dates in my database and I am getting a funny result when I perform a search and filter on from and to dates.

I have the following data:

Car 1 from 01/01/2006 - 30/01/2006
Car 2 from 01/02/2006 - 28/02/2006
Car 3 from 01/03/2006 - 30/03/2006

When I do a filter to report on cars from 01/01/2006 - 20/01/2006 only the first row is retrieved, however if I redo to the search from 02/01/2006 to 20/01/2006 expecting again only the first row. I instead get car 1 and car 2 reporting back as a match. It seems to be swopping the month round for some reason!?

If anyone could help me I would be eternally greateful.

I cannot for the life of me get this to work. I attach the code below:

On Error GoTo Err_report_Click

Dim stDocName As String
Dim stWhere As String

If CDate(searchstartdate) > CDate(searchenddate) Then
MsgBox "Please Enter an End Date Before the Start Date", vbCritical + vbOKOnly, "Report Error"
Else
stDocName = "DriverHistory"
stWhere = "driverNo =" & Me![driverNo]

' Check if we have a search start date
If Me![searchstartdate] <> "" Then
' This clause will be ANDed to the existing clause
stWhere = stWhere & " and "

' now check if we have and end search date
If Me![searchenddate] <> "" Then
' We have and end search date so we need to bracket the whole expression
stWhere = stWhere & "... Read more

Answer:Solved: Access 2003 Date Formatting Problem? - Can anyone help?

6 more replies
Relevance 66.42%

Hi all.

I hope somone can help this is starting to annoy me.

I am building a database for the charity i work for to track our collection boxes that are out and about.
I am trying to put a button in so it will pull out all boxes that have been out for greater than 30 days. To do this i am using the field "Date Out" in the query and the criteria is:

>Date()-30

This works but pulls our 200,000 results when there are only 1400 records in the table.

any ideas?
 

Answer:Solved: Access 2007 Date Query Problem.

7 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 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 65.19%

So far i have read you can't do this. So i'm wondering how you achieve this the correct way. I work for a gas field company and the database i am redesigning has a lot of fields that need validation, like well site names, company names etc. The person who built the original database had no validation at all, and you could find the word Production spelled 12 different way sometimes. Made it very hard to keep data readable.

So the part im working on now is the well sites list. I have a table called Well Sites. I have a main work orders table where they must choose a well site which is a lookup of the well sites table. This is fine and dandy, however. If they choose not to use a name in the drop down list, they can simply type anything they want, which is what i want to avoid. I want them to ONLY be able to type names that exist in the well sites table.

I was hoping it would be as easy as setting up a validation rule but from what google is telling me that is not even possible. So how do i control what user's enter. I do NOT want to type in a list of 600 well site names using OR for a validation rule. That is unmanagable, but i need to restrict what they can type so we don't get mis-spellings and mis-labeled wells.

Now i understand some things but some things i've just never done before prior to now. If i setup a one to many relationship between two tables then it seems to work like i want. Only values that exist in the other table can be entered into... Read more

Answer:Field Validation based on another tables data

6 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.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.55%

Hi, I searched the forums but can't seem to find this. I wonder if this is even possible.

But I have a cell with the following Data Validation settings:
Allow: List
Source: (following formula)
=IF($B$5="P1",$AW$16:$AW$19,
IF($B$5="P2",$AW$21:$AW$22,
IF($B$5="P3",$AW$24:$AW$27,
IF($B$5="P4",$AW$29:$AW$31,
IF($B$5="P5",$AW$33:$AW$34,$AW$4:$AW$14)))))

Now I have a form that I created, but to populate a Listbox, I want it to use the above Source If formula (but instead of $B$5, I want it to reference another Listbox, say, lstProduct.List(0)).

Is this even possible? Thanks!

Edit: I guess my question is, can I reference the Validation Date Source IF statement via a VBA object at all? Like, Cells($C$5).Validation.Source.Text or something (syntax completely made up).
 

More replies
Relevance 63.55%

HI

I can't remember for the life of me how to create a between query useing two times (12:00 and 22:30)

Please refresh my memory

THANKS
STUART
 

Answer:Solved: Access Between 2 Times Validation

I made a test query to show how it is done. Select design mode of your query Observe attachment, 3rd column. Please note, my Access is Hungarian. Where you see at the left side "Feltétel" it means condition or criteria in English. Also, probably time type data is handled differently. e.g. to separate hours from minutes I use colon ( : ), English Access may use something else.
 

2 more replies
Relevance 63.55%

Hi
Im doing a piece of work on MS Access and it is about a resteraunt.

I have a table called Tables(as in seating) with fields: Table ID, Seats, Location, Smoking

I also have a table for bookings with fields: Booking ID, Table ID, Staff ID, Date Booking Taken On, Date Booking Taken For, Time Booking Taken For, Number Of Seats, Customer ID, Additional information.

The 2 problems i have are with tblBooking,
1) Date booking taken on is set is Default Value "=Now()" and I wish to be able to set this so that nobody can change this field and it will always be this value unfortunatley due to system restrictions i cannot use user permissions.
2) I want to be able to set a validation rule so that if the number of seats needed in tblBooking cannot be greater than the number of seats for the table which is selected from the look up wizard linked to tblTable.

I know i have wrote it up very clearly but if nebody can understand it and help then i would be very greaful.
THANKS

STUART
 

Answer:Solved: Access Validation Rule

15 more replies
Relevance 62.32%
Answer:New PC had 2010 as date - problem?

Just got a new PC. Set it up last night and checked the date/time was right before proceeding. Realised this morning that although time and date were right, the PC had shipped with the year set to 2010!This means I now have 229 files with 2010 as their creation/modification date. Some I can delete as they relate to software I've installed, but some are registry? (is it still the registry on XP) files so I don't want to touch them. It's probably not a problem, but of course if I do searchs for recently created/modified files, it'll always come up with'em!! OK, so I can bracket the dates I search on, but this is far too time consuming!Also, Windows95 let you search for "files modified in the last X days" so you could narrow it down to very recent files. Why does XP only let you choose "1 week"? Can this be changedCheersDave

6 more replies
Relevance 62.32%

Hiya!

This is probably super easy, I just can't seem to figure out how to do it.

I have a Date/Time field in a table, which is in "Short Time" format. I want people to only be able to enter times in increments of 30 minutes.

Is there some way to put in a validation rule to "only allow times that have a 'minutes' value of '00' or '30'"?

Many thanks!
 

Answer:Solved: Access 2007: Validation Rule for Time

9 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%

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%

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%

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%

Hi,
I am trying to set up a field to increment for each new record. I know about auto increment but the way I have it set up their are two sets of numbers. 1 for the group number and one for the Item # of that group. The group number increments when a button is pushed. The Item number would increment either when a button is pushed or the user tabs through all the fields. Example 1-1,2-1,2-2,3-1, ect . I would also like their to be a way that if their is not data entered in a group or field that it can be either easily removed, or give an error that the form is blank and not go any farther. The code attached is in my database but it does not seem to work.
Thanks for looking,
Joe

Private Sub New_Item_Click()
Dim intnewrec As Integer
intnewrec = frm.NewRecord
If intnewrec = True Then
MsgBox "You're in a new record."
a = 1 + Item
Item = a
End If
End Sub
 

Answer:Solved: Microsoft Access Increment a field

16 more replies
Relevance 61.5%

Hello All,

I am fairly new to Access and can really use some help here. I have a form with a field "PO Number" that needs to be a required field before my user can use the print button.

All the other ideas I have seen seem to be very complicated to me as I do not do VBA and I am using a button with a macro that saves the form and prints the report. The form is a Sales Ticket.

All I want is to require the field "PO Number" to be required before it will save or print the report from the form.

Thanks in advance for any and all assistance.

Scooter1959
 

Answer:Solved: Required Field in Access Form

16 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,

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%

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%

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 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%

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%

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%

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 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%

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%

Hello,

Using Office XP Im trying to create a query that will ask for a message, then append this message to the end of a field on records where a 2nd field (a tick box) = Yes.

The code at the moment looks like this...
Code:

UPDATE [TABLE] SET [TABLE].[FIELD] = [FIELD]+". "+[:Enter Message]
WHERE [TABLE].TICKBOX=Yes;

This seems to work apart from where FIELD is null, on these occasions the FIELD remains blank.
TIA
Slimboy Fat
 

Answer:Solved: Append Message To Field In Access

15 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
Relevance 61.09%

Hi ,
I am using True bound DB Grid in my VB code and have a date column in one of them. I usually check for validate date(eg 11/11/1111 is valid date & 99/99/9999 is invalid) in BeforeColUpdate or BeforeRowColChange. However these events are called only when u loose focus to another cells in the grid. Now when i enter invalid date and directly click on save with out loosing focus to another cell, these events are not fired and system tries to save invalid date which raise error.
 

Answer:TDB Date Validation

Is there an "After Update" or "After Change" event that you can put it in?
 

1 more replies
Relevance 61.09%

I have an excel spreadsheet with a date column. The column format is *3/14/2001. I enter 08/01/2011. The formula bar reads 8/1/2011. The cell reads 40756. If I put the cursor in the formula bar the cell reads 8/1/2011. Place the cursor any place else cell revers to 40756. How can I get the date to display as a date in the cell.

Answer:excel 2010 date format problem

One of your options is messed up:Click on the Office Button in the upper left cornerClick on Excel Options, down the bottom of pop upClick on AdvancedScroll down to the section that reads: Display Options for this Worksheet:UN-Check the box that read: Show Formulas instead of their calculated results.Click OKThat should correct your problem.MIKEhttp://www.skeptic.com/

3 more replies