Computer Support Forum

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

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

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?

Relevance 100%
Preferred Solution: Access 2010 Date Field in Form Macro or ()Iff ??

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 (This link will automatically start a download of Reimage that you can save to your computer.)

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

10 more replies
Relevance 98.4%

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.

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

This form updates an underlying table:

The date field of the "Work_Hours" table is:

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:

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:

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

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

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

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

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

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?


More replies
Relevance 84.46%

Here is what I'm trying to do.

For lists Equipment in drop down box.
Whatever equipment that is select, the equipment type field needs to be updated from a table.

Is there a way to get a value from SQL statement?

SQL = "SELECT [Equipment Type] FROM OrderDetails Where " _
& " Equipment = '" & Me.Equipment & "'"
[Forms]![OrderDetails]![Equipment Type] = SQL

Answer:Help with access form (insert table value into form field)

Mhouser, if you are trying to "display" a value related to the Combo selection you can have thta value as an extra column in the combo and refer to it with simple VBA.
You should not populate a Field's actual value with that from the combo as that is duplication.
Can you tell me which one you are trying to do?

3 more replies
Relevance 79.13%

Good Day
I am fairly new to code and writing Macros.
I am workig in Excel 2010 and creatinfg an estimating tool for bidding condtruction project
My goal is to create a user control form (Done)
Parts DB (Done)
automate the creation of the proposals and project file using the user form
My issue now is creating the macros to input the dats

my list, combo boxes see the cell ranges by the range put in the control source that works fine
I currently can not figure out the Macro to input new date if the current infromation is not listed
example: col B=project Name. row 1 is title. B2 = bakery, B3 = Bank. I ned my next entry to input new information in B4 then auto populate a new qu0te number in col A

The user form has function over the entire workbook and is located in the VBA this workbook load upon opening

I apoligizes if my terms or explinations are not clear as I said I am really very new to this and need all the assistance I cane get

Answer:Excel 2010 User Form Macro

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

3 more replies
Relevance 78.31%

In Access, is there a macro or function that can be used or setup via a key combination that will sum the total of any numeric field of a table that I have placed my cursor in and display the total in a little window. I would like to use this instead of having to go through a query process.
For years I have used a DOS database Paradox 3.5. It has a function called "imageCSUM" that I have assigned to a key combination and will do this.
Has anyone done this or something like it that could be adapted.

Answer:Access Sum of field Macro??

7 more replies
Relevance 78.31%

I have a database which ultimately will have a couple of thousand records. The primary table has 30+ fields. I have lots of queries and connected reports to show various subsets of the data needed from the table. However, there are times when what is needed is all fields for a specific subset. Because the records sought often need to be filtered by several criteria, I've found the "Filter by Form" option to work well. I have a button on the main dashboard marked "Find Record" that automatically opens a search form in the "Filter by Form" mode. This allows me to enter information into as many controls as necessary, and returns exactly the right records after clicking on "Toggle Filters" on the ribbon. The problem is that ultimately I need to make this "Access-free". The goal is to create an application from the database without ribbons. I've created a button to run the filter, and another one to print the results, but when the search form is open in the "Filter by Form" mode, it greys out the buttons. I understand that there is a GotFocus command or something similar. Can anyone help with specifics, both the syntax of the command and where the command needs to be typed? Thanks a bunch...I look forward to your reply.

More replies
Relevance 77.9%

Hi there

I know how to do add a criteria to a Query. For example in the QBE pane in design view in the criteria row I enter this statement: Between [Start Date] And [End Date]. When I open up the form that is bound to the query, a message box pops up and I enter the required dates and I get the required data that is between the dates that I specify. However, I'm now finding out that this method is now becoming a nuisance.

Is there a way that I can have 2 unbound controls - one for the start date and the other for the end date in a criteria form. This way I input the dates I want for a particular customer and the form with the required data shows up. For example, I have already developed a criteria form for customers. I have an unbound combo box control that has customer names and when I click on a particular customer name, all the data for that customer is displayed in a form. In the row source of the unbound combo box control there is a SELECT .... FROM ... statement. I also have a button next to the unbound combo box control that opens up the form for that particular customer. Now I want to add two more Unbound controls in the criteria form, one for Start Date and another for End Date.

This way I can select a customer from the customer combo Box, select the start start date from the start date combo box and the end date from the second combo box control and get the desired data for that particular customer between the specified dates. I have attempted to do this bu... Read more

Answer:Access - Start Date and End Date in a Criteria form

Mario: What you want is called a query by form (QBF). I don't know how to do them myself, offhand, but here's the MS Technet article:

2 more replies
Relevance 77.9%


I have a table1 with a lot of fields a,b,c,d,e,f and so forth.
I also have a table2 with just a and b. (because you are not allowed to fill in value a if it is not in table 2.
I have a form that i use to fill in a,b,c,d,e,f
Is it possible to make the form fill in b based on the value of a ?

Answer:Inheriting from another field in access form

The answer to your question is Yes.
However as the data has to be in table 2 then the User should not actually enter the data in the Form for table 1.
Ideally the User would "Select" the data from table 2 and the only thing stored in table 1 is the ID Key value from table 2.
The selection process would be made using a Combo drop down box.
Normally the data from Table 2 would be on display in a Main form and the data to be entered in table 1 would be entered and shown on a Sub Form, usually in continuous forms mode or Datasheet view.

1 more replies
Relevance 77.08%

I'm having trouble creating a macro to insert the current date (static).

Here's what I'm doing:
View > Macros > Record
Name: "Date"
Store macro in: keyboard Shortcuts
Click on keyboard picture
Insert: CRTL+D & click Assign
Record: I have tried all the following:

(/@ "mm,dd,yy")

Then Stop Recording

When I hit CRTL+D I get exactly the code I put in instead of the date "02/12/13"

What code should I be inserting to get today's date?

Thanks, Mike

Answer:Create a Date macro Word 2010

Welcome to the Seven Forums.

start recording
select insert (upper left)
select date & time (upper right)
select the format that you want
click OK to exit the dialog box
stop recording

4 more replies
Relevance 77.08%

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 !!
ActiveCell.FormulaR1C1 = "=TODAY()"
Selection.NumberFormat = "mmmm/dd/yyyy"
Selection.Value = Selection.Value

Answer:Solved: Macro for date in Word 2010

6 more replies
Relevance 77.08%

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.


Answer:Solved: Required Field in Access Form

16 more replies
Relevance 77.08%

I have an Access form and want to search on a field that contains duplicate records. At that point the user could manually scroll through the records to find the one they need.

Does anyone know how this would work?


Answer:Searching in Access form on field with duplicates

16 more replies
Relevance 77.08%


On the form that I have on my Access Database, I would like to add a field in the form so that I can do a search by ID # and hence pull up the information for the record matching this ID #.

I am somewhat familiar with Access. I would really appreciate any help that you could provide.




Answer:Creating a search field in MS Access Form

Doesn't the form wizard automatically give you the id field to type into? Are you saying you want a dropdown box instead of the regular field-type box?

3 more replies
Relevance 77.08%

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.

Answer:Solved: Adding field to Access Form

10 more replies
Relevance 77.08%

I have been put in charge of creating a database to record all of our quality audits on parts. (a simple part inspection)

Most info will be entered into the Audit form named frmAudit. However, if the parts fail inspection another form is opened from frmAudit called frmNCM.
All the info entered into the NCM form needs to be linked back to the audit form. So, my NCM table has a field for AuditID.

The problem is I can not get the NCM form to open and automatically fill in the AuditID in the NCM form from the Audit form.

My current code:

Private Sub btnNCM_Click()
On Error GoTo Err_btnNCM_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmNCM"

stLinkCriteria = "[AuditID]=" & Me.[AuditID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit Sub
MsgBox Err.Description
Resume Exit_btnNCM_Click

End Sub

Thanks for the help

Answer:MS Access Open form new record ID field

6 more replies
Relevance 76.26%

The OLE field on a form in Access 2002 SP-2 will only show the icon (can double click to open a photo) instead of the content, even though the "Display as Icon" box is not checked in the "Insert > object" instruction, and the field property is set to "content." This is on a new computer. The old computer had Access 2002 SP-1 and there was no problem with viewing the content of a photo in the form. I can copy and paste a picture from a Word document ok, but I have to explain that to others who use the database. Any help appreciated.

More replies
Relevance 76.26%

This seems like it would be a common task in Access but I can't find any reference to it.

I have a combo box with three choices, Kamazaoo, Grand Rapids, and Traverse City. The field name is location. The location is entered only once but there is no limit to the amount of records that can be added. When one record is finished Access defaults to the previous location and starts a new record.

I know how to do a make table query but can I do a make table query based on the fields value? For instance if the entry person is finished entering data for Kalamazoo a button could be pushed that would make a Kalamazoo table.

Building the Ultimate site list for PC support.

Answer:Make table based on form field in Access

16 more replies
Relevance 76.26%

I have the following code behind the After_Update event of the textbox...

On Error Resume Next

Dim strName As String

strName = StrConv(Me.Name, vbProperCase)

Me.Name = strName

The code is used to make sure that the last name is in proper case. The code works fine as long as the last name is a typical name: smith, williams, doe, etc.

Is there a way to get the proper case to work properly with names like McMillian, MacSmith, McCord, etc.?

Answer:Solved: Access Form - Name Field (Proper Case)

15 more replies
Relevance 76.26%

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


I am looking for some help on my code to lookup the largest incident # then add 1 to it when a new form is selected. I created a new record button on my form but would also like to have the new incident number populate when the new record comes up.

What I have is
Private Sub cmdnew_Click()
If Me.New Record Then
Me.Incident# (not really sure if I should put the field name here or not?) =Nz(DMax(Incident#, External_Theft),0)+1
End If
End Sub

Any help is greatly appreciated.

The field name is Incident# and the Table name is External_Theft

Answer:Access 2007 Increase Incident# by 1 in field on form

You may want to consider changing the name of your field since the # is used for dates in certain instances.

You could create a query to do Max(Incident#)+1 and then call that query from your forms onLoad event handler to populate the field you want with that number.

2 more replies
Relevance 76.26%

Hi There

To make a long story short say I have a table called Table1 with only 3 currency fields called Deposit1, Deposit2, Deposit3 and TotalDeposit. I then create an input form based on Table1. On the form, In design view, I use all 4 fields. On the TotalDeposit field on the form, I do a calculated field as follows:


I then populate the 3 fields with currency - $100, $200 and $300

On the form the calculated field shows a total of $600. However, when I go back to the table, the TotalDeposit field has a zero? How come? How do I get the calculated field on the form to appear on the table??


Answer:Access problem in calculated field on form to appear in Table

Hi Mario

How exactly have you set your TotalDeposit field to be populated with the sum of the other three fields? That is - where have you typed [Deposit1]+[Deposit2]+[Deposit3]?


2 more replies
Relevance 76.26%

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

Hi I am working on an existing database and trying to change the input method on a form to a combo dropdown box. I am trying to use the existing data in tblProjects and the data is in a column called "PM" I sort of have it working but instead of showing my "PM" data it is showing the data in the first column of the table???

Any ideas how to get this to work? It's been a few years since I messed with Access so I am a little rusty. I have included a screenshot.

Answer:Change form field entry to a combo box in Access

14 more replies
Relevance 76.26%

I have created a database in Access 2007 with 4 tables, Customers, Products, Orders, and Order Details (the specific items for each order). I know you don't want to dupliate data and "calculated fields belong in reports and queries" but we change the prices frequently and I need the UnitPrice from the Product Database to "update" to the Order Details Table on the day the order is input.

I have a Order Input Form with 2 sub-forms and my relationships are set one-to-many on primary keys. All other queries and forms work great. I have tried looking at the Northwinds database to determine how they do an "AfterUpdate" event procedure but I haven't used VBA since Access 97 and am not able to convert it for use with my form.

Help is appreciated!


Answer:Access 2007 Update Table Field from Form

Cosla, have a look at the database that I posted here
at post #5.

3 more replies
Relevance 76.26%

I have a control in a form that calculates the Gross Monthly Income based on amount entered in Annual Income (=IIf([AnnIncome]>0,[AnnIncome]/12,0)). Can the results of this expression be used to update the Gross Monthly Income field in the underlying table?


Answer:Access-Update field in table from expression in form?

10 more replies
Relevance 76.26%

Sorry - I forgot to tell you that I am running MS Access 2003 SP2 on an XP machine


Answer:Entering Multiple values into an Access form field

Delete this thread by using the "edit" button & add this info into your other post.
It will only confuse people if your keep 2 running.

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


Answer:MS Access - autofill date field

6 more replies
Relevance 75.44%

Hello, i am writing for the first time on this forum and i appologise for my english.. I'm glad that i have found a part of the solution for saving one word file to difrent locations with these code Sub SaveX2()Dim strFN As StringDim strFX As StringDim strL1 As StringDim strL2 As String'setup two locations - ending with /strL1 = "H:/"strL2 = "C:/Temp/"'get filename from userstrFN = InputBox("Enter file name without extension", "Save to Stick and HDD")'check for no file extensionIf InStr(1, strFX, ".") > 0 Then strFX = Left(strFX, InStr(1, strFX, "."))End If'get existing filename and extract file extensionstrFX = ActiveDocument.Name'test for file extension on current documentIf InStr(1, strFX, ".") = 0 Then 'none so use a default strFX = ".doc" Else 'use existing strFX = Right(strFX, Len(strFX) - InStr(1, strFX, ".") + 1)End If'create the filenamestrFN = strFN & strFX'save to stick firstActiveDocument.SaveAs FileName:=strL1 & strFN'save to HDD secondActiveDocument.SaveAs FileName:=strL2 & strFNEnd SubNow my question is can i insert the date and hour automaticly as file name in stade of typing the name of file in the input box ? please can you help me ? Thank you.

Answer:insert date and hour in macro code for word 2010

First, a posting tip:Please click on the blue line at the end of this post and read the instructions on how post VBA code in this forum. Using the method described in that How To will make viewing the code easier for all.As for your question, you cannot use a "/" or a ":" in a filename so please tell us what you want the filename to look like.e.g. 4/8/2013 12:45:23 PM is not valid.4-8-2013 12_45_23 PM is.How would you like the filename formatted?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

5 more replies
Relevance 75.44%

Hi All!

I have a form that calculates an employees current age in a Text box (based on today's date and their date of birth). I created the form to act as an 'entry' form for new employees in an HR database.

I have a field called 'age' in a table that I would like to populate with the result of this formula. How can I do so when the 'Control Source' property of the text box on the form is holding the formula!? That's where I would normally link it back to the table!

Thanks in advance for any help!



Answer:Can an access table field populate from a calculated cell in a form?

You use simple VBA code in the Date of Birth field's After Update Event Procedure and set the Age field's Control Source to the table field to store the data.

1 more replies
Relevance 75.44%


Could anybody help out with MS Access 2007?
Is there any way that the prompt parameter value which is entered to generate a report is captured somehow and its value is passed to be displayed in a form's field. The report is generated from this form.

Specifically, the form has a subform (datasheet). One of the columns is Date. This subform is populated throughout several days. User needs to generate a report that includes the data from this subform every day (or every other day). User enters Date as a Parameter Value to filter data to a report. User does not remember what is the last Date for which report was created.

If a form's field will display the last Date for which report was created this would be solution.

More replies
Relevance 75.44%

MS Access 2003 crashes after I add a new record and try to edit any form field. I can add a new record in the underlying table and then go to the form and edit any field. Can't catch the error with a breakpoint either - it seems the new record is not being initialized properly from the form that creates it.

Other forms in the same database do not have this problem. No server or network or sql involved with this database.

(Microsoft Office Access has encountered a problem and needs to close)
AppName: msaccess.exe AppVer: 11.0.8166.0 AppStamp:46437912
ModName: msaccess.exe ModVer: 11.0.8166.0 ModStamp:46437912
fDebug: 0 Offset: 0020ba36
Tech Support Guy System Info Utility version
OS Version: Microsoft Windows XP Home Edition, Service Pack 3, 32 bit
Processor: Intel(R) Pentium(R) D CPU 2.80GHz, x86 Family 15 Model 4 Stepping 4
Processor Count: 2
RAM: 499 Mb
Graphics Card: Intel(R) 82945G Express Chipset Family, 128 Mb
Hard Drives: C: Total - 147644 MB, Free - 64485 MB; D: Total - 4963 MB, Free - 2775 MB;
Motherboard: Intel Corporation, D945GBI, AAC99325-202, AZBI52800690
Antivirus: avast! Antivirus, Updated: Yes, On-Demand Scanner: Enabled

Answer:MS Access 2003 crashes after I add a new record and try to edit any form field

9 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


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%

I am trying to create an AutoOpen VBA Macro in Word 2010 files for automatically inserting the same dates into 2 different text boxes when I first open the file. Ideally, I would want 1 file to do this always for Yesterday's date (whatever that may be) and another file to do this for Friday's date (whatever that may be).

Reason for this is because our mail room gets mail the day before I get it on my desk and I need to date stamp it using a Word 2010 template where I have 2 text boxes (please see attached file--oh, doesn't seem like I can attach the file???) for Tuesday through Friday. Right now, I am using the Macros I have created (1 for Yesterday's Date and another Macro for Friday's Date which is Date() -3).

I want to use that Macro logic to put that into the Text boxes I have when it first opens? Please help me with this anyone......

It doesn't seem like I can attach the file so here is the VBA code:

Sub AutoOpen()
' AutoOpen Macro that places the cursor at the last position of edit (not default of top left) (will work with Protected View)
If Application.ActiveProtectedViewWindow Is Nothing Then
End If

' InsertTextBox1 Macro
Dim Shp As Shape
Set Shp = ActiveDocument.Shapes.AddTextbox( _
Orientation:=msoTextOrientationVertical, _
Left:=22, Top:=252, Width:=25, Height:=170)
Shp.TextFrame.TextRange.Text = "NMM RECEIVED: "
Set Shp = Nothing
Selection.InsertAfter Format(Now() - 1, "dddd, ... Read more

More replies
Relevance 74.62%

Hi, I would like to display a message box when a total on a subform does not match a field in the main form.

I have a form (FormA) with a subform (SubformA)

I need to compare the total on subformA (located on SubForm A footer) with a field on FormA

I am entering a number on the subform and in the On Exit Event I entered me.requery to requery the data and update the total in the footer - then I entered the code to compare the two numbers

This is the code I am using (edited):

If [FormA field] <> [SubformA total] Then
Msgbox ("Amounts Do Not Equal")
End IF

The problem I am having is the refresh time of subformA footer - the field takes longer to refresh or requery (I tried both) and the message box displays even when the numbers are equal

Thanks in advance

Answer:Access 2007 Compare subform total with field on main form

6 more replies
Relevance 74.21%


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?


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

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;

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

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

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.


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


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

I have a table with a field called "current fund balance" which is intended to be a data entry field. I have a query with multiple calculated fields. Both the table and query share a common field. I'd like to have a datasheet form to where data from the query and table are combined and have that field "current fund balance" be a data entry field. I'm having the hardest time figuring this out.

I've read a form/subform would work but I need to be able to see all the records at once. And so, datasheet with all fields would be best. Anyone have any thoughts?

Answer:Solved: Having a form field in Access to enter data based on a query with calculated

16 more replies
Relevance 73.8%

I have 2 buttons, New Record and Duplicate. When these buttons are clicked on, I want the cursor to move to the Employee ID field after executing the command. Can anyone help please? If it is just a bit of code can you tell me exactly where to put it? Any help would be greatly appreciated!!

Answer:Solved: Move cursor to required field when form is complete, access 2007

Solved this myself. I created a macro at the end of the Buttons Embedded Macro 'On Clilck' event. Macro:

GoToControl cbotextboxname

Works like a charm.

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

InStock - Check Box

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%

I have a 2003 Access database form that I inherited that is driven by a form. The form is limited to macros that run queries. I can't get the form to open WITHOUT running the first macro?

Answer:Access database form and macro

close the access database. Then hold down the left shift key on your keyboard. While holding the shift key double click the access database. continue to hold shift until access fully loads.

2 more replies
Relevance 73.39%

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:
="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?


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

7 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

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")
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 73.39%

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


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%

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

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


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


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%

I need to duplicate a record on my main table by using a form off the main table. I know you can simply use the wizard with a command button but I don't want to use the wizard. I need to write a macro for the command button because I want the macro to do other things like add a message and so on. I have tried the setvalue option but it didn't seem to work. I want to duplicate the entire record into a new record. My primary key is based on an auto number so that should be the only thing that changes. Do you know how to write a macro to duplicate a record in a table?

Answer:Access. How do I duplicate a record on a form using a macro.

I don't have instructions for doing it with a macro but if you modify this and put it on the On_Exit event of the last control on your form the values will be carried over to the new record by default.

With CodeContextObject

.YourFirstField.DefaultValue = """" & .YourFirstField & """"
.YourSecondField.DefaultValue = """" & .YourSecondField & """"

End With

Just replace the YourFirstField, YourSecondFiled with the names you are using and it should work for you. If you insist on doing it with a macro I think you have to use the set value like this:

Action > SetValue
Item > [Forms]![FormName]![YourFirstField]
Expression > """" & [YourFirstField] & """"

I think that is how you do it with a macro. Let me know if you need more help.

[Edited by Talismanic on 06-06-2001 at 09:58 PM]

3 more replies
Relevance 72.57%

I running access 2003 but i am creating for 2000

Bsaicly its a stock control system. And i have a field call "Total number ever in stock" Which basicly refers to how many of such a product has been purchesed for the shop altogether.
Reasons for this are complicated but its stems from the batch procecsing method of data colection but its not important.

Simply i want to create a macro or simmilar so that when the user enters a Product ID and Quantity to be added into a blank non table related form the macro will add that quantity to the existing quantity for that product in a table.

So far i have created a form in design view and have inserted two unbound text boxes "ProdID" and "Amount" and have a table with field names "Product ID" Total number ever in stock"

Thanks all help appreciated.

Answer:Adding to fields through use of macro and form - ACCESS

If I understand you correctly, you have an unbound form that a user enters a product ID and qty to be added to a related record that already stores a summary amount. You can do this using that method, but it will have some data integrity challenges. If someone enters the wrong amount, you won't know where it came from or how much the qty was for the wrong amount.

To perform the update from an unbound form can run an update query from a button.

Here's a SQL sample that you could run from a button

UPDATE Prod_Totals SET Prod_Totals.Product_ID = [Forms]![frmQtyUpdate]![txtProdID], Prod_Totals.Total_Qty = [Forms]![frmQtyUpdate]![txtTotQty]+[Total_Qty];


That being said...I would consider approaching it from a different angle.

1. Create a table of Item_Events and Products

Table Name:

Item_Event_ID (Autonum) Key
Qty (+/-)
Event_Type (Purchase / Sale / etc..)

Product_ID (key)
Create a form based on Products with a subform of Item_Events. When you need the total qty use a summary query.

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


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!



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

Hi I wonder if anyone can help. I want the back colour in an organisation name field to change to green if they are a member company based on a "member yes no" check box this possible. I am pretty good with access generally but conditional formatting and code are not something I know much about. Any help gratefully received. PS both fields are held in the same form and table.

Thank you

More replies
Relevance 72.16%
Question: Access Date Macro

I want to automatically list the date everytime I open up a particular access record. I know how to do "Now()" and this works fine for long dates but i want to list
#1: SHORT DATE (Jan 23, 2004)
#2: MONTH ONLY (Jan 2004)

Its a database to track phone calls and I need want to be able to quick list them as to date called and month called in a report.
Steve Passmore

Answer:Access Date Macro

It isn't entirely clear what you are trying to achieve. What I don't know is what field(s) currently exist in your table/form for date...

For the sake of argument, let's say you have a field that contains the date the call took place.

You can have that field on a report or form as many times as you want (make sure only one of them is editable) and each one can display the data differently: one for input is straightforward Short Date, the next one, in the field's properties>format, you could put "mm yyyy" (without the quotes) and make this one non-editable, and it would give you the Jan 2004 view.

In a report, though, you can control how you view records by "grouping and sorting".

Hope this makes sense,


1 more replies
Relevance 72.16%

I've developed a database in Access 2010 and am using the Navigation Form for users to access the different forms and it's working very well. The problem is we still have some workers who are on Windows XP boxes with Office 2007 and the Navigation Form won't work for them. I've been asked to develop an Access 2007 Switchboard which will mimic the Navigation form so there is some continuity between them and so that once all the systems have been upgraded the users willbe familiar with the interface. I easily designed a form with the look or the Navigation Form but haven't been able to figure out how to handle opening the different subforms on the one main form. Is it possible to place an Unbound Frame on the form and with a click of a button open a subform in the Frame or have a button just open a subform? How would that be coded?

Thanks for your assistance

Answer:Solved: Access 2007 Switchboard & Access 2010 Navigation Form

I am a bit confused because Subforms normally open when a mainform opens.
I am not familiar with the Access 2010 navigation form so I am not sure what you are trying to do.

2 more replies
Relevance 72.16%

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,

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

I have a form and I want to have a combo box that will Give today’s Date 5/1/2005 as a selection.
That would be today’s date.
If I select this date it will place this Date in another field That I have formatted as a short Date.
This date must remain in this box even if I close the program.
I tried to use a query but when I go to the combo box it shows a blank cell.
It does enter today’s date in the cell I want it to but you cannot view it as with other combo boxes.

Answer:Access-Getting Date in a form Via Combo Box

i'm not sure how to do the date with a combobox like you mentioned above, but here is another way to do this:

Private Sub txtDateCreated_Click()

'if there is not a date in this field, then when the user clicks in the box, the current date is displayed.

If IsNull(Forms![frm1]![txtDateCreated]) Then
Forms![frm1]![txtDateCreated] = Date
End If

Put a label near the text box saying something like 'click to enter today's date'.

i like this method because instead of needing both a combobox and a text box, you only need a text box.

i am assuming that your combobox would only contain the current date. in that case, you don't need it. is that right or were you going to display other values in the combobox along with the current date?

2 more replies
Relevance 71.75%

I'm doing a quick-and-(very)dirty design as a proof of concept. During this process I fat-fingered the word Description, as Ddescription. No problem, I know what it means, right. But it annoyed me, so I changed the underlying table (where the error occurred), deleted the control from the form, and added the (now correctly spelled) control.

However, every time I try and ruin the form it wants a value for the now non-existent field Ddescription. I simpy click-through, and the form runs fine. When I look at the available fields in the form's View query's Field List, it still shows Ddescription, and i seems to think it's coming from the underlying table.

Any ideas on how I can remove this phantom field? I can't find anything called "The_View_for_form_FooBar" anywhere, but I know it must exist somewhere...


More replies
Relevance 71.75%

I attached my database what i would like to do is create a form when an end user will enter a corresponding user ID from that I wish to populate textbox fields with Agent Name, Supervisor, Location, Dept *all seperate text boxes of course* I have attached the database if someone could help me do this it would be great. Let me know if you need any further details to help get me through this.

Answer:update field A in form based on entry in field b

Welcome to the forum, before answering you question can I suggest some improvements to your database.
Currently you do not have any "Key", "Indexed" fields in your tables and there are no relationships set up between the tables.
Also do you really need the Archive Table?
The data looks like it has been exported in from a spreadsheet, relational databases work quite differently to spreadsheets, so to make the most of their advantages you need to correctly relate your Tables.
Your UserID Roster also has some disconnect between the Field Names and the actual data in them, particularly the Name and Type feilds.
In the Supervisor Table the Birthdate does not seem to have Translated correctly.

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

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

Hi all. I'm attaching an image of a macro that came useful in many of the Modal Pop-ups that were included in Access 2007 templates.

It saves the form, then opens a form that you specify at the ID of your hyperlink. It will then requery the form and search for the records for that ID.

Is there a VB that does all this in Access, so I can get away from using the macro?

More replies
Relevance 70.93%


I have a number of problems with a database that I am creating, one of which is how to automatically add a date to a field in a form.

This is a database that will be used by multiple users, and one of the fields is called "Date last updated". I have imported the data and locked the fields that shouldn't be changed (thanks to a reply to a previous post on here). I thought it would be straightforward just to add =NOW() to the field on the form, but that doesn't make any difference. I have also tried setting the default value of the field in the original table to =NOW() and "binding" the field in the table to that. Doesn't work either! Does anyone know how I can do this?

I also have another question for you....see my other post!!!



Answer:Solved: Access 97 automatically add date to a form

Ed, in the Form's On Current Event Procedure enter the following code

Me.[Date last updated] = Now()

3 more replies
Relevance 70.93%

Hello! I'm hoping someone out there can help me. I'm creating a basic form, it would be nice if when a record is created the day you created the record could autofill into place. Does anyone have any suggestions out there how I could do this?

Thank you,

Answer:MS Access Date Auto-Fill Form

You could set the default for the date field to Now().

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

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,

More replies
Relevance 70.52%
Question: Access 2010 Form


I have the following tables:

• Clients
• Social Workers

I’m seeking assistance in creating a social worker form that lists each of the social worker’s clients.

The relationship layout is attached.


More replies
Relevance 70.11%

This question was asked by someone several years ago, but I did not find the complete answer.

I need to know how to create a QBF using a date range as part of the criteria.

I have a form to run a query. The form has a combo box to limit "approved" or "pending" “new” purchase orders (PO) (criteria one), but I would like to add a second criteria for POs created between "07/01/2009" and "07/06/2009" (criteria two).

I want the end-users to be able to select the date range (enter the start date and end date). I don't want to limit their selection.

I know how to use the date range in "Design View" of a query (ex: >=[Start Date] And <=[End Date], or,
Between #07/01/2009# and #07/06/2009#) but I don't know how to implement that into the Form.

I know I need to put something like the following in the query criteria field for PO_Created_Date:

Between [Forms]![Formname]![BeginningDate] And [Forms]![Formname]![Text_EndDate]

But how to make it work in the form?

Please help!



Answer:Access 2003: Query by Form using a date range

Yuan, welcome to the Forum.
You appear to understand the query requirement, especially the "Between [Forms]![Formname]![BeginningDate] And [Forms]![Formname]![Text_EndDate]" part.
So which part don't you understand?
as long as the you put in the name of your Form in the Fromname brackets and you have 2 fields on the form called BeginningDate and Text_EndDate that Criteria will work.

3 more replies
Relevance 70.11%

Hi all,

I have problem with query a date range... I know how to use in "Design View" of a query (ex: Between #01/01/2004# and #05/07/2004#) but I don't know how to implement that into the Form, for eg: In Criteria: Between (Forms!FormName!From_textbox) And (Forms!FormName!To_textbox) but it just returns nothing...

Please help!


More replies
Relevance 70.11%

A while back you (I don't remember the exact members) helped me create a query by form in Access 2000... Thank you. Now I need to know how to create a QBF using a date range as part of the criteria.

I know how to use the date range in "Design View" of a query (ex: Between #07/01/2004# and #07/06/2004#) but I don't know how to implement that into the Form.

As an example, I would want to pull all records of "Apples" (criteria one) sold between "07/01/2004" and "07/06/2004" (criteria two).

Please help!!!

Answer:Access 2000: Query By Form Using A Date Range

7 more replies
Relevance 70.11%


I am writing a WEB Access 2010 application and I have just discovered that I cannot use a SQL SELECT statement within my Query. The SQL STATEMENT creates a new column called CondPrice within [CFLHPtbl] populated with a lookup and match from a comparison between [EQPPrice] CFLHPtbl. The SQL SELECT below works GREAT, but I can NOT publish it to SharePoint. I am new to Access 2010 Web DB, can anyone show me how to accomplish this within a Web Macro?
(select eqpprices.price from eqpprices where productnum = cflhptbl.condmodel and eqpprices.profilenum = testval ) AS CondPrice

*Please note that “testval” is just placed there for testing. I want to be able to replace the “testval” with a selected value from a combo box.
Thank you in advance for any help or suggestions.

Answer:Access 2010 Web Macro – HELP!

7 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'm the only guy in an $800 mm company using Access to mine data and produce reports so I'm on my own. I won't insult you by claiming to be a Tech Guy, but for the most part, I can make Access do some pretty neat things (please don't talk VBA with me because believe it or not, it's Greek to me). That said, I'm stumped. An application that has been running nicely for the past 6 months has decided that it no longer wants to cooperate.

My Vendor Performance management system won't execute from an icon on my desktop. I've replaced it but nothing works. I "can" however launch the application manually via Explorer. All of the ODBC connections to the primary database (Informatica) are fine and current; I can query each one of then without problems. All of my queries and reports work correctly when I launch them individually. Here my problem:

When I launch the Master Control Form (just a simple form not associated with any table, with icons linked to executable macros like collect data records, print reports, etc.), Access crashes. It doesn't go NOT RESPONDING, it just hangs. CTRL - ALT-DELETE!!!!

I have upgraded to 2013.
I have relinked ODBC tables.
I have increased the amount of free space on the disc (not an issue).
I have created a new structure and copied over everything but the Master Control form and then rebuilt that form in the new structure. That form will work as long as I have only 2 or 3 macros. When I get upto 3 ... Read more

Answer:Access 2010 form won't open

11 more replies
Relevance 69.7%

Just getting into programming with Access 2010 and I have a form which has several buttons which I change the caption depending on what I am working on. I cannot get the form to refresh with the captions though. I have tried form.refresh with no luck. If I interrupt the code and check the values in the immediate window I get the caption I set which is different to the screen. What am I missing? Textboxes update OK but not button captions.

Answer:Access 2010 form will not refresh

I also have occasional problems with the refresh of screens in access. Here's a couple to try.

On after update : [Forms]![YourFormName].[Refresh] (simular to your Form.Refresh)
Me.Refresh - seems to work in places.

1 more replies
Relevance 69.7%

i have several tables that keep track of different aspects of a process. 1 table is for the grower, all information about him is in this table, another table is for the Growers deliveries. they are both tied togeather by a Grower Number. When i bring up a form for the Grower Deliveries i don't want to have to add dupicate data, I want to select the grower number and have the Grower Name, Phone Number and contact come up in the form. And not have to store dupicate data. I can't figure out how to achieve this and i know it is possible. Its driving me nuts as i just can't remember how to achieve this.

Answer:Access 2010 Form Issues

andrewm270, welcome to the Forum.
The conventional way to what you describe is to use a Mainform with the Grower's details on and a Subform with the Deliveries, using the Grower Number as the Master/Child link.

1 more replies
Relevance 69.29%

Hi all

I am following the syntax as according to instructions but even if its a . or a ! between the [Table Name] and the [Field] the Macro won't find the records where the If statement is true.

I've attached the database with the macro I have created. Can anybody please help?


Answer:Access 2010 Conditional If Macro

7 more replies
Relevance 68.88%

I have a password form with a text box and a command button, on click of the command button it checks that the correct password is entered in the text box, if it is it opens another form, if not a message box pops up informing an incorrect password has been entered.
When the form opens it is maximized.
I want to password form to close when the next form opens, 'DoCmd.Close,Form1,acSaveYes' after 'DoCmd.OpenForm,frmMainEmp' on the On Click event results in nothing happening at all.
'DoCmd.Close,Form1,acSaveYes' and 'DoCmd.Maximize' on the On Open event of the new form results in the password form maximizing and 'frmMainEmp' not opening.
Any idea what I am doing wrong here?

Form1 code

Private Sub Command4_Click()
If Text2 = "admin123" Then
DoCmd.OpenForm "frmMainEmp"
MsgBox ("Password Incorrect")
End If
End Sub

frmMainEmp code

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err


Exit Sub

MsgBox Error$
Resume Form_Open_Exit

End Sub
note that I have removed my attempts to close Form1 from all coding at this stage.

Thanks in advance,

Answer:Solved: Access 2010 - Password Form

This is what I use for my Login form called Login

DoCmd.Close acForm, "Login", acSaveNo

and it is before the second form is opened not after it.

1 more replies
Relevance 68.88%

Sorry for all the posts but as I work through the design I think of new ways to make it better/easier which is the main requirement of it. So here's my question, I have a data entry form to at items (Movies/Games) into the inventory. The form comprises of ItemID, TitleID, Title, Producer, Category, Type, HireFee and Copy. I am unsure on TitleID at the moment, but where the title is entered once into the tblItemDetails table, it can be entered numerous times in the tblInventory table so I am considering this TitleID to reduce data usage. Now, is there a way to repeat the entry numerous times without having to re-enter data? As in, there's five copies of a movie, input five entries to tblInventory in one go, rather than having to re-enter all the information again and just have a new ItemID and Copy number for each.

That, or can I just have it so that I enter a new title, e.g. 'Fallout 3' along with all the details, then create a new entry and having title as a combo box being typing and select 'Fallout 3' and have all the other fields auto filled? Because all that will be different is the ItemID which is an autonumber and the copy number...

Sorry if that doesn't make any sense.

Thanks in advance,

Answer:Access 2010 - Data Entry Form

Mark, the TitleID (like all repeated data) should only be entered once.
ie the data goes in a table for the Game/Film and then the titleID is used in the inventory along with a Quantity field ie 5 copies) which is updated when the item is issued or returned.
The issuing and returning should be logged in a separate table.

1 more replies
Relevance 68.88%

I want the customer's discount to be spelled out in a new field called discount on a form. Example: If the [price] is 100 and the discount amount [D] is 20, then [discount] would say: -20=80
If [D] is blank then then [discount] should also be blank.

Having no idea what I'm doing, I tried modifying what others have done for other fields and came up with this:

=IIf([Forms]![combo]![D]<1,"",IIf([Forms]![combo]![D]>=1,"-" & [D] & "=" & [price]-[D]))

Doing this [discount] says on the form: #Type!

[price] and [D] are long integers, [discount] is text
Thanks for your help!

Answer:Showing prices on a form in MS Access 2010

You should not need the reference the Form, just the field.
Personally I prefer to use VBA code for this type of calculation.

1 more replies
Relevance 68.88%

Hi guys,

I am an intermediate user of Access, i can create basic databases etc.

I am creating a finance database (there's a lot of data) which has different tables (7 to be exact) which have forms for entering the data. I also have created reports and queries to show any unpaid items for each of those tables.

I am now wishing to create a search form so the person whom i'm making it for can search date, the business and weather or not it's been paid to give him more of an overview of the information. However, i seem to be getting a wee bit stuck as for some reason access doesn't like querying multiple tables, or i'm doing it wrong. (i'll go with the latter ... occam's razor and all that)

If i've not explained it correctly, my apologies, feel free to ask any questions. Thanks for your help!

Answer:Solved: ACCESS 2010: Search Form

13 more replies