Computer Support Forum

How to record date/time when specific field is modified in Access 2007

Question: How to record date/time when specific field is modified in Access 2007

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 100%
Preferred Solution: How to record date/time when specific field is modified in Access 2007

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

Relevance 87%

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

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

I am trying to set up a form with a land map showing sites 1 through to 100, each site is linked to its own record (by means of site number e.g. 001, 002 etc) which has a field for an option box selecting either Available, Pending or Sold. I want to colour code each site on the map by means of an object (either label, graphic etc). Each choice is colour coded, ie OLE001available is green, OLE001pending is yellow and OLE001sold is red. etc for each site. I think I can do this using visible yes or no, i.e. if site 001 is available then OLE001Available is Visible - Yes, while OLE001pending and 0LE001sold are not visible? How do I link each OLE for each colour and each site to the relevant record and field? so that the correct colours are displayed on the form map.
 

Answer:access 2000 linking object to specific record field

14 more replies
Relevance 98.81%

Hi, I have a table that has a collection of group names. Each group name can be assigned to multiple issues - which come from another table.

I made a form to create a new group name and a list box of all the available issues. I would like to have a command button "Create new Issue" so the user could add an issue to the list directly from that form through an inputbox.

Here is the code I have so far:
Code:

Private Sub Command15_Click()
Dim strTmp As String
Dim x As String

x = InputBox("Please enter a new issue", "New Issue")

If x <> Null Then

strTmp = "INSERT INTO [Issues] ( [Keyword] ) " & _
"SELECT "" & x & "" AS [Keyword];"
DBEngine(0)(0).Execute strTmp, dbFailOnError

IssueList.Requery

End If
End Sub

This is code I borrowed from somewhere else and tweaked. I get the inputbox, but the data I type in doesn't get created as a new record in my Issues table.

Thanks!
 

Answer:Access 2007: New Record in Multiplevalue field from Inputbox

Not sure what you mean. Do you want to have a dropdown list that they can add to if the item is not there? That is treading on giving users too much control over something that should be static. If a "group" needs to be created make an admin form for adding it to the table along with validation to prevent duplicates.
 

1 more replies
Relevance 95.94%

we use outlook to view messages in the military. these messages are most likely refrenced by their date time group. in the message ( body of the email ) it will state the DTG.. example DTG: 291323Z Apr 10

i would like to make a field in my view so i can have the messages displaed by DTG. is this possible? if so how?

thanks
 

More replies
Relevance 95.12%

Hi, this probably something simple, but I just don't know it.
I have two forms, the first called directory, the seconde called details.
I want a button on directory that will pull up the detail form of the specific record.

Now in earlier versions of Access I used the command button wizard to run Form Operations - Open form - [Form name] - [Open for specific data] - selected the matching fields. And that worked like a charm.

Since I've upgraded to 2007 though, it always, and I do mean always, opens the proper form, but selects the last record in the form.

I've read elsewhere where this is 'new' feature of 2007, but this doesn't serve my purposes.

Any suggestions as how to fix this would be greatly appreciated.
 

Answer:Access 2007 - searching for specific record in two forms

If you use the standard VBA created by a Command Button to go to a specific Record does it work?
Especially if it was created in Access 200-2003?
 

3 more replies
Relevance 95.12%

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

Is it possible with Access 2010 to have a date field that would change to current date whenever another particular field is modified. For example, we have comment field and we would like to have another date field that would change to the current date when the comment field has a change.
 

Answer:field modified date

Yes you can do it with simple VBA code, you could also add it to the comment itself, so it appears at the beginning of the comment.
 

1 more replies
Relevance 94.3%

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

Hi guys,

Something weird is happening. Some of my movie files (file type doesn't matter) became corrupted; when I play them in VLC they cause errors, they were just fine before and nothing has changed. I noticed in Explorer that these files have no Date Modified field, it's simply blank; I've never seen anything like that.

I did a chkdsk on the drive and see no errors. What could it be ?

Thanks.

Answer:Blank Date Modified Field ?

VLC should rebuild the Index, but that is a temporary fix. Depending on what format they are, there is "DIVFIX" and "Meteor MKV repair" you can try to run the faulty content through which of those is appropriate, you'll have to google it, cant remember the links.

3 more replies
Relevance 90.61%

While searching for some information in some old files, I noticed that every time I open a Word document, using Word 2007, the "date modified" shown in Windows Explorer automatically updates and changes the file properties to reflect the current date and time. I make no changes to the file and at no time am I asked to “Save” the document, which would justify the update of the “Date Modified” field.

This happens in both Vista and XP.

Is there any way I can change this setting? I only want the date modified to change when I actually make a modification to the document.
 

Answer:How to stop updates to Date Modified field in Windows Explorer

7 more replies
Relevance 88.56%

The thread title says it all. I want to specify specific start and stop dates as a filter, not the general "last month" or "last year" type offered.

Any help?

EDITED: Ignore this request (I don't know how to delete it.) Reason: Modifying the filename doesn't change the Date Modified field. :-(

Answer:Can I search for Date Modified between two specific dates?

Please read this post: How to search

Kari

1 more replies
Relevance 82.82%

I am using Word 2007. Each time I open a .doc or .docx file, the date modified changes, as displayed in Windows Explorer. This happens when I just choose file open, and then exit, without ever doing anything with the file.

Previous versions of Word would appear to do the same thing. However, when the file was closed, the date modified in windows explorer to revert back to the previous date. Word 2007 causes the current date accessed to become the date modified.

This makes it very confusing, as it looks as though I am making changes to the file, when I am not.

Help appreciated...marty
 

More replies
Relevance 82%

I have a folder, that within that folder, items are listed by date modified. Great, that's exactly what I want. But, I have one folder within that folder that I'd like to always be at the TOP of the view. The folders go back to 2013. Is there a way to manually change that one specific folder's modify date to xx-xx-2012 so it will always remain at the top?

I've already run the reg fix to turn off auto-arrange, so, I can manually drag it to the top of the folder, but, randomly every once in a while, it returns to it's "proper" place by date.

Thanks all!

R

Answer:manually manipulate "date modified" field?

This tool...
Home | Attribute Changer
...should do what you want.

2 more replies
Relevance 82%

Looking at the Last Modified Date in Windows File Explorer, the Year field shows the year 2036 on some files but not all. The Creation Date matches the Last Modified Date. This happens to folders and individual files.

I first noticed this when I did a fresh install of Windows 10 Anniversary Update. Does anybody have an idea what is causing this. Any help would be appreciated.

Attached: a snipped image of Windows File Explorer showing the problem

Answer:Date Last Modified year field is set to the year 2036 on some files

Hello wguimb,

Looks like you're from the future. LOL

Double check to see if your system date may be set incorrectly somehow.

https://www.tenforums.com/tutorials/6...dows-10-a.html

9 more replies
Relevance 82%

Is it possible to search for items modified on a certain date and the time during said date, e.g 24/01/2011 3.57PM? What would the entry need be?
Thanks!

Answer:Can one search for date and time modified?

See if the following helps: Advanced tips for searching in Windows

For your example, the syntax will be: System.DateModified:=24/01/2011 15:57

4 more replies
Relevance 82%

If I am in the wrong forum, please let me know.

About a month after I started using it, the Say the Time clock program I bought stopped launching the management menu when I right click the date/time field in the task tray (notification area).

Therefore, I can't use or edit the program.

Neither the touchpad nor the cordless mouse works.

Other than this problem, my computer works great. Every single item that's meant to be right clicked works for me.

Is there a way I can generate a log to show the software manufacturer (Provenio) that it's their problem and not mine?

I am a novice user but I can follow instructions.

However, I don't want to put my computer at risk if I make an entry error.

Or is it obvious that it's a software problem?

I've been using computers for 18 years and I've never had this problem until I bought this software and used it for about a month.

I just wanted a conclusive way of putting the ball back in the manufacturer's court.

Thanks for any suggestions.

Regards.

Answer:Say the Time Date/Time Field in Task Tray Doesn't Launch Menu

You can use VEW by Vino Rosso http://images.malwareremoval.com/vino/VEW.exe
and save it to your desktop

Double click it to start it Note: If running Windows Vista or Windows 7 you will need to right click the file and select Run as administrator and click Continue or Allow at the User Account Control Prompt.

Click the check boxes next to Application and System located under Select log to query on the upper left

Under Select type to list on the right click the boxes next to Error and Warning Note: If running Windows Vista or Windows 7 also click the box next to Critical (not XP).

Under Number or date of events select Number of events and type 20 in the box next to 1 to 20 and click Run
Once it finishes it will display a log file in notepad

Please copy and paste its entire contents into your next reply

5 more replies
Relevance 81.18%

My XP Pro has recently stopped updating the modified date / time to reflect when I modify a file.

The problem relates to the application that updates the files and not the file themselves. E.g.:

MS Notepad, Word, Excel, Outlook, Microangelo, Photoshop, Winmerge - all update the Modified field as expecetd.

MS Wordpad, EditPlus, TopStyle, Notepad++, FastStone Image Viewer - all do *not* update the Modified field.

I tried ending all non-critical tasks, but problem persists.

The problem does not appear when running in Safe Mode.

I've used system restore to go back as far as I can but no fix there. I can't find any reference to the problem on the web.

Any suggestions very gratefully received.

More replies
Relevance 81.18%

The CEO of the company called me in his office today saying that ALL of his files show a modified date of January 23rd at 6am. I checked event viewer and his computer wasn't even on at this time. The weird thing is this happened with his desktop last year when it was powered off as well. I have no answer for him as well as no explanation. He is irritated either way though.

Any ideas as to what would cause the file modification date to change although the computer WAS NOT powered on?

Edit: Lenovo Thinkpad notebook, Windows XP SP2 and NTFS.
 

Answer:URGENT: Modified date shows time when PC was off

These are local files? Shared at all? Any actual modifications, or just the stamp in the properties?
 

13 more replies
Relevance 80.77%

Hi All,
I am using LUMIA 1320 that has Lumia Cyan.
Sometimes the time and date is getting reset to a specific date, i.e. 22nd May 2014
Due to this all reminder, alarms and new messages are not coming in sync.
Is this a virus? Does it have come from an app. If that is the case then it questions Microsoft App policies...
Anyone who has encountered this issue and has done something to eradicate it let me know.

Answer:Why is my phones time and date getting reset to a specific date?

First, windows phone can never get a virus. Its the safest platform after iOS . And no, a particular app can't affect the system's time and date. I think your should try to manually set the time and date in the settings. And see if it works.

3 more replies
Relevance 80.36%

Here is the question posed to me by a friend:

"One more odd-ball question? if you click on the time in the bottom right hand corner of my office computer, it has the correct time and date. However, when I save new files and look at the date (or transfer to FTP), it says June 2099! I wouldn?t think of it being a huge problem except when I try and back-up files on my computer to CD, it won?t save these because it doesn?t recognize the date. I tried google searching the problem and couldn?t find anything. Ever heard of anything like this?"

I had no answers for my friend- I have never seen this before. Has anyone else ever seen this happen?

Answer:File Date Modified and System Time don't match

I've tried to locate info on this, but no luck so far...

4 more replies
Relevance 80.36%

Hi,

Would anyone please be able to advise me how to use Windows 7 search to find all files on a PC that were created or modified after a selected time and date?

I can do this easily in XP but just cannot get this work in Windows 7.



Thx
Auld Bint

Answer:Search For All Files Created or Modified After A Time & Date ?

If you have explorer.exe open, for eg. Computer. In the search bar at the top right of the window when you click on that A pop down will show and you can add "Date Modified + Size" filter.

2 more replies
Relevance 79.95%

How do you insert a date field in Word 2007?

Answer:How do you insert a date field in Word 2007?

In Office 2010 at least, it should be under the "Home" or "Insert " tab. Then there should be a sub-category like "Date" or "Time".By the way. In the future, you'll get better office results here:http://www.computing.net/forum/offi...Ask and Answer. The way of learning.Dell Dimension 8300Intel Pentium 4 HT @ 3.20 GHz4 GB RAMNvidia Geforce FX5200

2 more replies
Relevance 79.54%

When using Windows Explorer, a directory listing can show a number of different columns of course but it appears the default is Date Modified .. which is fine. However, it doesn't show "seconds". The same appears to be the case for the command line output for "dir".
The only solution I've found so far is to put the directory path in Google Chrome - which is a bit of a pain but it does work. So the information *is there*.

How to get Windows Explorer to display the time with seconds?

Answer:Display file date modified time including seconds.

Try this:

Control Panel> Region and Language> Additional Settings> Date tab

Change the dddd setting to ddd manually

Apply and OK your way out

See if that works, if not, see if this helps

Change File Date TimeStamp From File Properties In Windows

A Guy

3 more replies
Relevance 79.54%

Hi there,
I came across this thread (now closed)
http://forums.techguy.org/business-applications/473412-solved-adding-date-time-last.html

Adding Date and Time Last Modified to footer of an Excel worksheet.

It adds the date to each worksheet.
Could someone please show me how to modify it to add the date to each worksheet ONLY IF that particular worksheet has changed?

Thank you in advance.
 

Answer:Solved: Adding Date and Time Last Modified to an Excel worksheet

Hi there,

Define "change". As in, any change whatsoever? And did you use the workbook before save event? Use a workbook event to get what you want, just use the workbook sheet change event (still in ThisWorkbook module), and be sure you change the activesheet reference (in your linked thread) to the sheet object name (in the parenthesis of the event, I believe it is "Sh"). That should pretty much be it. If you need more help, post your code and tell us exactly what you are looking to do in a little greater detail.

HTH
 

3 more replies
Relevance 79.13%

Hello,

I write quite a few manuals, and I've recently upgraded to Word 2007.
I know there must be a way to do what I'd like.
I have a section where I have a table in the manual, and I want to be able to type a part number in one column, and have the description for the part automatically pop up in the next column.

I've tried searching different forums, but I keep hitting on answers on how to have it automatically fill in the same field.

Any help is greatly appreciated!!
 

More replies
Relevance 78.72%

Hello, I need help with the following:
I have a field (called Date_Time) which displays for example 1/31/2005 12:00:00 AM. I would like to run a query that converts that value to '200501' in a created field.
I tried the following below but I keep having problems.

select Account_Number, Date_Time,
concat(year(Date_Time), month(Date_Time)) as Date_Time_Modified
from table
where Account_Number = xxxx

Any insight into this would be greatly appreciated. Thank you.


 

Answer:Concatenating Date and Time Field in SQL

Just wanted to add some more examples:

Account_Number: Integer Type
Date_Time: Date Type
Date_Time: Modified: Character Type

Account_Number Date_Time Date_Time_Modified
3246 1/31/2005 12:00:00 AM 200501
4920 11/30/2008 12:00:00 AM 200811
5932 6/30/2011 12:00:00 AM 201106
... ... ....
 

1 more replies
Relevance 78.72%

Is there a way to add Date and Time Last Modified to an Excel worksheet footer, so I can can tell from the various "work in progress" hardcopy printouts what is the latest version?

Thanks-Jamie
 

Answer:Solved: Adding Date and Time Last Modified to footer of an Excel worksheet

16 more replies
Relevance 78.72%

The Windows Live Mail *.eml files time stamp is getting changed to the current date when the file is selected in the Windows Explorer! I save backup copies of important E-mails to folders on the Hard Drive and then synchronize them with the same folders on other LAN Hard Drives for backup archives. When these E-mail files are selected or opened from the Windows Explorer in the Windows 7 OPS the Modified Date Time Stamp is changed to the current date automatically! This action creates a real problem when doing later synchronizations of all of the folders! The date will not be the original date of the E-mail and the original file will be over copied with the file with the newest date stamp. I stopped Indexing on the *.eml files and that stopped the time stamp change when overcopy synchronization, but if the file is selected during a viewing in Windows Explorer then the Time Stamp is updated to the current date again! A solution to this problem would be greatly appreciated if anybody knows of one. Thanks - - - Shorto

Answer:*.eml Time Stamp - Select File in Windows Explorer Changes the Modified Date

The Date and Timestamp changing issue in Windows 7 has a two part fix!
1. Disable the Indexing of the *.eml file type in the Indexing Options located by Clicking on "Control Panel" -  "Indexing Options" - Advanced Button - File Types Tab - scroll down to the .eml file type and uncheck the Box then click OK to finish, so
that the Email type of files won't be Indexed.
2. Delete the Registry Entry listed below, be sure to Export and Save the original Key so it could be Restored if something goes wrong!  \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\PropertySystem\PropertyHandlers
 and Delete the .eml entry
 
That should fix the problem with the changing Date/Timestamp of Email Files. For more information read CarlS's WindowsSeven Fourm thread where I also have more input:
 
 http://www.sevenforums.com/browsers-mail/35334-eml-files-timestamps-updated-constantly.html

 
This should give you a better understanding on the problem and the Fix. - - - Shorto

9 more replies
Relevance 77.9%

Greetings

It's been a long time since I last posted here, but hopefully all you wonderful people will be able to help...

Here is my problem:

A customer of mine has a SQL database where one of the fields is a date/time field that looks like this:

13/02/2003 14:32:58
14/02/2003 08:59:10
15/02/2003 17:09:37

Now, I need to delete all the records for a particular day (One of their HD's failed, and they had to restore the data and lost a day, but the entries are still in the database, and can not be re-entered until the old entries are deleted).

How do I structure a query to delete all the records for a particular day? Preferably, I would like a SELECT statement first, just to check that the records found correspond to the delete criteria, and then the DELETE statement.

Thanks

Reuel Miller

The Ex-NT moderator on this board

Every morning is the dawn of a new error
 

Answer:SQL Query help required with date/time field

Greetings

Not to worry chaps, I figured out the answer all by my lonesome

the statements I used were:

select * from {database name} where {date field} > '14 February 2003' and {date field} < '15 February 2003'

delete from {database name} where {date field} > '14 February 2003' and {date field} < '15 February 2003'
cheers

Reuel Miller
 

1 more replies
Relevance 77.9%

I am trying to build a sales database. In it I also want to schedule delivery days. I want the database to record for each customer the days that delivery cannot be made. I have a multi selection list box, but even though the control source is the field I want, it doesn't record a value.

Answer:MS Access - Multiple options record in 1 field.

I think a list box is just for bringing up record data that already exists.The simplest way would be to create a seperate table for the days and link it (relationship) to the main table using a unique field such as the order number.You can have the two tables showing on the same form too.

1 more replies
Relevance 77.9%

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_btnNCM_Click:
Exit Sub
Err_btnNCM_Click:
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 77.9%

Hello,

I am hoping someone can help me with this little access problem. I have inherited a project that uses access as a reporting tool using linked tables for a SQL database.

I have created 2 forms (header with a detail form imbedded). In the header form there are 2 comboboxes. In the first box you can select the AE Name and that causes the second combobox to list only the sponsor names entered for that AE. After you select the Sponsor Name the following code runs to display that selected sponsor in the embedded detail form.

Private Sub Combo2_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SponsorName] = '" & Me![Combo2] & "'"
If rs.NoMatch Then
MsgBox "Not Found", vbOKOnly
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing

End Sub

It works with only one complication - if there is more than one AE with the same Sponsor Name it picks the first one it comes to - which may not be the right AE and therefore not the right record. Unfortunately, the table that is providing the information does not have one field that is a unique key (or I could use it instead of the sponsor name to findfirst), the key is made up of 4 fields - the AEName, SponsorName, Scenario#,Org#. I would be happy to get the exact record (provide all four pieces of info) or the first record that matches both AEName and SponsorName, but I have had no luck in creating that code.

H... Read more

More replies
Relevance 77.08%

This one has got me. Has anyone figured out a way to do this easily? I can create a new column and extract the right characters and make it work ... but just wondering if there is a quicker way out there!

Thanks in advance for your time/help ...
 

Answer:Excel: Extract hour ONLY from a date/time field

Ok, got it to work ... but not sure if there is a better way.

I created a 2nd colummn and used the =RIGHT(D107,5) to isolate the time (10:32) from the date/time field (JAN 12,2014 10:32)

I then created another column and used =TIME(HOUR(C107),0,0) to pull out the hour ONLY in 10:00 AM format.

Is there a quicker way out there?
 

2 more replies
Relevance 77.08%

I have a db that has 5 tables each linked cascaded (SW <-SWCD <- DGRP <- EQT <-DATA). The Main form SW (single view) has a subform for each table (dataview) but they are linked to the previous subform like above (not main form except SWCD). I am trying to figure out the best way to search on the EQPT field (from the EQPT table/subform) and display the correct results in the Main form with the subforms. Can any one help?
 

Answer:Solved: Access Find Record on Subform Field

8 more replies
Relevance 76.67%

Hi

We have an Access application that holds employees' table. When new employee comes, the email stating the name of a new employee and the link to this Access app. being sent to a supervisor. When supervisor starts the app, it opens the list of all new employees for a supervisor to choose from.
What my boss wants is, that access will open a new employee record by itself, depending on the name from the email. In other words, he wants to open the specific record by the link from email.

Is it possible to do this?

Thanks,
Barbos
 

Answer:Solved: Access 2003 - Opening specific record in Access table via link in Outlook

16 more replies
Relevance 76.26%

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

Hi,

I need to update one field in the most recent record in my table; the new value for the field needs to come from prompting the user. I've been able to update the field to a hard-coded value using the following SQL in an update query. I just can't figure out how to prompt the user for the value instead of using the hard-coded value of 2. The Urgency field is of type Integer, and the Id field is of type AutoNumber. Thanx in advance for your help.

UPDATE tableA
SET Urgency = 2
WHERE (Id=
(SELECT MAX(Id) FROM tableA));
 

Answer:Solved: MS Access 2003, Update One Field in Most Recent Record

You can use a form for the user to enter the Urgency value, preferably using a Combo Box to select from a list of possible values.
You can also use an InputBox function but it is not as versatile as a Form.
 

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

Does somebody know a program which makes it possible to record a radiostream on a certain time and date?

example:
record radiostream every friday from 18:00 to 20:00

Answer:record radiostream on a certain time and date

Quote:





Originally Posted by LincolnX


Does somebody know a program which makes it possible to record a radiostream on a certain time and date?

example:
record radiostream every friday from 18:00 to 20:00




musicmatch

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

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

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

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

More replies
Relevance 74.21%

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

George
 

More replies
Relevance 74.21%

Hello

Access 2003
Windows XP

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

Thanks

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

Answer:Access 2003 - Format field to Short Date

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

3 more replies
Relevance 73.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:
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 73.39%

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

The rule is

Is Null Or <= Date()

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

The error message is:

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

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

Any help would be gratefully received!

Thank you
Alison
 

Answer:Solved: Access 2010 date field validation problem

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

2 more replies
Relevance 73.39%

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft® Windows Vista™ Home Basic, Service Pack 2, 32 bit
Processor: Pentium(R) Dual-Core CPU T4200 @ 2.00GHz, x64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 3002 Mb
Graphics Card: Mobile Intel(R) 4 Series Express Chipset Family, 1308 Mb
Hard Drives: C: Total - 152624 MB, Free - 101762 MB;
Motherboard: Wistron, 3612
Antivirus: Symantec Endpoint Protection, Updated and Enabled

I had my laptop hard drive replaced and part of the agreement included the installation of MS Office Professional. When I went to pick-up the laptop I asked for the MS Office product Key Codes for my records and registration. The store owner said he had already registered the software and it was set to go, just bring it back if I ever needed to reinstall for any reason. I was suspicious as a legal installation would include this information.

When I got the laptop home and started it up, I checked to see the licensing information on MS Office Professional and it was registered to IUWare (University of Indiana), I have reported this to their ITC Support and other security and policy departments. Long story short I am in process of filing a FBI claim against this vendor and a VISA dispute as well. I want to know how I can access the time/date stamp on the initial installation of MS Office Professional on my hard drive and all changes made since that initial installation. The first night I had run time errrors and took the lap... Read more

Answer:Root Record of Software time/date stamp of installation/changes

If you go Control Panel > Progarms and Features, you can see the date of installation of all software. If the date column isn't showing, right click on the column headers and checkmark "Installed On'.
 

2 more replies
Relevance 72.98%

I figured out the solution. Thanks for looking.
Moderator, please delete this thread.
Have a form (transaction form) containing 8 buttons.
Each button will open another form.

Each of the 8 forms being opened are based on the same table as the original form.(Too much data in the table to be shown in 1 form. Couldn't make it work by breaking some of the data into another table and trying a 1 to 1 relationship.)

I would like to have the forms open to the same record as my original form. Right now they are all defaulting to the first record no matter what record is selected in my original form.

I would like to accomplish this in VBA code.

Thank you in advance for any help.
 

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%

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

Hello, I've been noticing this over the years. When Daylight Saving Time occurs (whichever one, moving the clock forward 1 hour or moving it backward 1 hour), ALL my files' "Date Modified" detail move forward or backward 1 hour.

For example, I have a document file called asdf.docx and its Date Modified detail is: 9/26/2015 4:00 PM
When Daylight Saving Time comes around to move the clock forward 1 hour, that file's Date Modified is now 9/26/2015 5:00 PM. That's not correct, it should stay at 4:00 PM. What the hell is happening?

More replies
Relevance 72.16%

Hi I am totally new to Access 2007 and have only had a small experience with access 97 and 2000.

I am creating a Data Entry Form, And i am looking for a entry field to become visible when someone ticks a Checkbox above it. Now i think i have done this a long time ago in a previous version, but my memory is not the best and for the life of me i cannot figure out how to do this.

Any help would be appreciated.

Regards

KSL
 

Answer:Access 2007 - Field Visibility

KSL, it is best done using VBA, place the VBA in the Check Box's After Update Event Procedure.
The VBA will be something like this
if me.checkbox = -1 then
me.Fieldname.Visible = true
else
Me.Fieldname.visible = False

where checkbox is the actual name of you checkbox and Fieldname is the actaul name of the field you want to make visible.
 

1 more replies
Relevance 72.16%

Hi,
I have the following problem. I want to create a little database in access 2007 to track some orders.

I have products table, Clients Table, Order Status etc.

So I have to create a table as follows

[Client Order No Type of Product NameofProduct Qnt DateofShipment etc...

I created a multiple value field for nameofProduct based on Products table. Because one order may have 10 products.

So if the the order consists of Product1, 2, 3 for one Order from 1 client I have to track the quantities for each product.

How can I set different quantity for each item in the multiple value field. It allows me to set 1 value in Quantity field and it appear for all the selected products. maybe I haven;t made the right thing.

Please help me. thank you
 

Answer:Multiple value field Access 2007

Hi,
Your products table should have only columns which are product properties "ID", "Name", "Description"....

Additionally I would create two more tables:
- "Orders" with columns "Order No", "ClientID", "ShipmentDate", "ShipmentAddress", etc,
- "OrdersProducts" where you link order numbers with product IDs, so three columns are necessary "OrderNo", "ProductID", "ProductQnt"

Now just add as many products with the same order number and different quantity for each product as you like to table "OrdersProducts".

Many tables for the first sight complicate the project, but if you consider it carefully, you'll see benefits of such a structure
 

5 more replies
Relevance 72.16%

Hi Im Very new to Ms Access. I am trying to build a simple database That list Client details for invoicing and installation address for service calls.
I have fields Listed as:
Client add1, Client add2, Client add3, Client add4, Client post. These fields i fill in manually
I then have a field that is a Yes or No field Labeled as copy Client to Installation
I have Fields Listed as:
Inst add1, Inst add2, Inst add3, Inst add4, Inst post.

I want to Manually fill in the Client Add fields and then if the Installation address is the same set the copy field to yes and have it copy the details from the relevent Client add fields to the matching Inst add fields.

If the Client address is different from the Installation address i want to set the copy feild to no and enter the installation details manually.

Any help greatfully received
 

Answer:MS Access 2007 Copy Field

Lyntonw, welcome to the forum.
Can I suggest an alternative strategy, as it is not normally a good idea to have duplicated data in a database.
Can I suggest you make your "copy" field in to a "Use Client Address" field?

If you want to continue with the "copy" version then you need VBA code to copy the Address data over the the Inst fields.
It tacks the format of
me.[Inst add1] = me.[Client add1]
me.[Inst add2] = me.[Client add2] etc
The field names have to be exact, including correct capitals where they are used.
 

1 more replies
Relevance 71.75%

Hey,

I have a form which has several fields. I have created another section on the same form for filtering information. I have an unbound textbox called txtUnloaded that when operating correctly will filter out all records who field "Unloaded" has the specific Date. I cannot get the date one to work corretly for the specific date. I have another filter for between dates but the txtUnloaded date does not work. any help would be appreciated. Here is the code I have.


Private Sub Command150_Click()
Dim strForm As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strForm = "InfoInput"
strField = "Data_Entry"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " < " & Format(Me.txtEndDate, conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " > " & Format(Me.txtStartDate, conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate, conDateFormat) _
& " And " & Format(Me.txtEndDa... Read more

More replies
Relevance 71.75%

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

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

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

I have some forms where they field turns black after you have entered and tabbed to the next one. You can only view the entry in the field if you put your cursor back in that box. I have copied the format from other fields that don't do this, yet it still happens on certain fields and not on others. I have checked the background colors for these fields and still don't see why this is happening. It tends to happen more on fields where I have mabye tried different background colors. I can't seem to maintain the one color I have chosen. It keeps going to black after updating.
 

Answer:Access 2007 Forms Field Format

Is there anything in the Event properties of the box? Say for After update or On Enter?
 

1 more replies
Relevance 71.34%

I'm relatively new to access, only been working with it on and off for the past few months or so and have run into a little problem now.

Basically, a date is entered in a field on a table called "Date of Entry", and another value is entered in a different field called "Serving Period". Now I have another field called "Expiry Date", and basically it's the value (date) of "Serving Period" added to the date in "Enter Date". I suppose that it would possibly have something to do with the Default Value of the "Expiry Date", but that's where I'm confused. Of course, I need not make it automatic and let the user do the math himself, but I'd rather it was done this way if possible.

I'm using Access 2007 on Windows 7.

Any help appreciated.
 

Answer:Default Value for table field in Access 2007

9 more replies
Relevance 71.34%

Hi - I've set up a simple database for some survey work. Data tables are linked by a primary key consisting of a 10 to 12 digit location code (picked up from a drop-down list) and a 2 digit survey number - all stored as text. Rather than use compound relationships I would like to combine these into a single primary key.
I know calculated fields in tables are wicked...! but how can do I do this automatically in ACCESS 2007. I can combine the two easilly in a calculated control in a form.... but not directly into the primary field. Although the result does appear in the control - I get an error message telling me I can't have a null entry in the primary field. Do I need to set up an event procedure???? Thoughts much appreciated
 

More replies
Relevance 71.34%

My Access 2007 database contains records with a text field of up to 255 characters. these records are being imported into a system that allows up to 39 characters for this data. I'm seeking help to create a query(queries) or macro(s) that will create additional records with each one containing the next 39 characters in the text field. For example, a record contains 125 characters in its text field. I need to end up with 4 records such that record #1 contains the first 39 characters, record #2 contains the next 39 characters, and so forth.
 

Answer:Parsing text field in Access 2007

6 more replies
Relevance 71.34%

I have a memo field in my application that the user needs to put a lot of text in with numbered items that require indentint. The finished report looks awful because she is forced to space between the numbers and the text instead of a clean indent. I am using the runtime version of Access 2007 field. I need find a text editor that will hold formatting and keep it in the field for the report.
 

Answer:Text Editor for Access 2007 Field

8 more replies
Relevance 70.93%

I have MS Access table "myTable" with field BegDate, AssignedTo, etc..fields. Try to update the record but i got an error "Data Type Mismatch in Expresssion"

Data in "txtStart_Date" is copied from form frmName1.StartDte
- [Forms]![frmName1].[StartDte] = [Forms]![frmName2].[txtStart_Date]

I try to update the table "myTable" with below code executing in Form frmName2

strPID = Me.PID
strAssignedTo = Me.AssignedTo
strStartDate = Me.txtStart_Date

SQLScript = "UPDATE MyTable SET MyTable.AssignedTo = '" & strAssignedTo & "'" & _
"WHERE (((MyTable.PID)='" & strPID & "') AND ((MyTable.BegDate)= '" & strStartDate & "'));"
I got an error "Date Type mismatch in expression".

I used the code below with "#":
SQLScript = "UPDATE MyTable SET MyTable.AssignedTo = '" & strAssignedTo & "'" & _
"WHERE (((MyTable.PID)='" & strPID & "') AND ((MyTable.BegDate)= #" & strStartDate & "#));"
I don't get any error but table was not updated.

If i'm going to eleminate the the code: AND ((MyTable.BegDate)= #" & strStartDate & "#));
It works but all record with the same PID was updated. I need only a specified record to update.

I appreciate if anyone can help me.
Thanks a lot.
 

More replies
Relevance 70.52%

Hi,

Over a month ago, I posted this topic here:

http://forums.techguy.org/business-applications/954178-default-value-table-field-access.html

However I never had the time to try out the suggestion. I've finally gotten a holiday though, and so gave it a go. Here's the code I'm using in the After Update Procedure, as advised by OBP:

currentyear = Year([DateofEntry])
newyear = currentyear + Me.[PeriodLength]
Me.[ScheduledDateofRelease] = Left(Me.[DateofEntry], 6) & newyear

It does work, so thank you for that

However, it does produce some odd errors. First of all, it only updates the "ScheduledDateofRelease" after I have clicked "Save" on the form, and once I try to close the form access gives me an error saying that:

"You cannot save this record at this time". In the same dialogue box it says "If you close this object now, the data changes you have made will be lost. Do you want to close the object anyway?"

After clicking yes on that box, and navigating to the table which is the source for this form, I find that the new record has indeed been added, contrary to what the error message was saying.

Another odd thing that occurs is that once I enter a new record into the form and click save, I can't go backwards or forwards through records on the form and am instead presented with the error stating "You can't go to the specified record."

Any help appreciated
Slikbaz.

edit: I've just rea... Read more

Answer:Solved: Default Value for table field in Access 2007

16 more replies
Relevance 70.52%

Hi,

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
Erica
 

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

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
http://forums.techguy.org/business-applications/931652-vba-ms-access-2007-a.html
at post #5.
 

3 more replies
Relevance 70.52%

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

Gentlemen,
I have a subform with the following fields: name of the step, the scheduled date, place and date rescheduled date. I'm having trouble making a statement in a query
that has the following criteria: need to know what the step is smaller than today's date between the date scheduled and rescheduled date and the date performed without value.
if there are any other dates scheduled and rescheduled less than today, I need to know only the name is the first step with the date due.
I think I need to create a field in the query to the term, but I'm not getting. Any help is welcome. Thank you.
 

Answer:Access 2007 consultation with field for multiple criteria

Can you provide an example of the data and what result you need, as it is difficult to visualise from your description?
 

2 more replies
Relevance 70.52%

Hello all,

I am a not a novice computer user by any means, or even a novice MS Office user. I am, however, brand new to Access.

We are currently in the process of creating a database with which to track complaints that we have received, so that we can ensure they are processed in a timely manner with all the appropriate steps taken. We would like to have a unique case number assigned to each complaint, automatically populated when a new complaint is entered into the database. I've created and populated the database from the Excel spreadsheet that we had been using to track these complaints.

The problem I'm having however is creating the autonumber in the "case" field that we want! We want our complaints to be auto-populated with a case number "XX-YYYY" where XX is the last two digits of the year in which the complaint was entered and YYYY is an autonumber, starting with 0001 and going up from there.

Can anyone help me with how I can make this populate correctly? I tried using the following in the Format mask in design view, based on the in-program help:

"10-"****

with the thinking that if necessary I can create a database file for each calendar year (useful for archiving purposes at least). However, ~every~ entry shows up as "10-" (with nothing after the hyphen) until I click on it (at which time a seemingly random, but sequential, 8-digit number shows up, starting with "69313633").

Please help!!
 

Answer:Access 2007: Help with creating custom autonumber field

8 more replies
Relevance 70.52%

I have a file that i import into access 2007 and i was wondering if i can take part of that file name and put it into a field in access? For example here is one example of a file name:

"20140211_agent_statistics.csv"

I have done some research on this but cant seem to find the answer when numbers change all of the time. I just need to grab the numbers on this file name. However, these numbers change all of the time. Does anyone have a solution for this? Thank you in advance. Any help and code is much appreciated i am very new to vba.
 

Answer:How to import part of filename into a field in access 2007

All I can think of is to write a vbsript that rewrites the file appending the desired part of the filename to each record(not to hard but another step). Better would be to have the creator of the file add the string to each record..
 

1 more replies
Relevance 70.52%

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

As a part of my data base I want to issue receipts for a charitable income tax donation.

I want the field to increment by 1.

I have it set to show the format year in the field ie. 2011000 but what I want it to do is assign the number consecutively by the year.

So that when the year changes... 2011 to 2012 it changes the number to 2012xxxx amd increases the number by 1.

the final 4 digits should start over from 0 each January 1.

Attached in 2007 format and 2003 conversion for convenience.
 

Answer:Solved: Access 2007 Auto incremente a field

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

Assume I have a (log)file and (in another directory) a shortcut which points to this file.

Each original file and its shortcut have their own last modified timestamp (as shown in WinExplorer column).

Unfortunately when the original file is modified the timestamp of the depending shortcut file IS NOT modified too.

Is there a way to let the shortcut be touched as well?

There is no need of an immediate update of the last modified timestamp of the shortcut object.

It can be implemented by a batch script which runs later over the whole partition with a touch command or similar.

Peter

Answer:Update last modified time of shortcut when original file is modified?

Not sure if this will help...

You might be interested in this:

Last Access Timestamp - Enable or Disable in Windows

2 more replies
Relevance 70.11%

Can someone help me with this query? I am trying to pull the record with the most recent date. I'm clearly not grasping the concept somehow; what am I doing wrong here? Created an overly simple example, attached.
 

Answer:Solved: Access Query Pulling Record with Most Recent Date

You should not have the FundID and the Nav in the query because their "Grouping" stops the Max from working.
Unless of course you want the max date for each fund.
You should have just the EffDate and then create a query based on that query and the table joined by the date to bring in the other data.
 

3 more replies
Relevance 70.11%

Help! I am using a Seagate Central 4TB NAS drive attached to my router via Ethernet. It has been working fine so far for all kinds of file activities. But whenever I modify a file in one of its folders, the creation date for the file also updates and is
changed to the same time as the new modified date.
Maybe relevant details:
I can move/copy folders to the drive and they maintain their created dates.
It happens for both a private user share on the NAS and for the Public share on it.
It happens for at least image files and text files.
I am able to edit files across two computers on the network just fine, the create date stays the same.
This happens from a new Windows 10 machine and Vista machine both.
I notice sometimes the date created seems to stay same until I access the file the next time.
I can't update the NAS firmware because of error.

If it could be a permissions problem, I have been unable to change it from:
Everyone: Special Permissions only
nobody (Unix Group\nobody): Special Permissions only
nogroup (Unix Group\nogroup): Special Permissions only
(Those were there automatically, they are not something I manually put there. I get "Access is denied" whenever try to add permissions or change ownership.)

I have Googled many phrases of this problem far and wide and come up with nothing. I really need to save the original create date because that's how I sort them in programs like Windows Photo Gallery or Picasa. Does anyone know what's goin... Read more

More replies
Relevance 70.11%

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

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

Would this help? enter link description here

1 more replies
Relevance 70.11%

The title is self-explanatory. So far, I found a software that is able to change this called 'Attribute Changer' but it only changes the date modified in the folder and, when you go into the actual word application > file > info, the date created and modified are not changed. Any help would be greatly appreciated! Thank you!
 

Answer:Changing 'date created' and 'date modified' in Microsoft Word 2013

7 more replies
Relevance 70.11%

hello to everybody

I need batch code to check if date is modified in three files and that date should be compared with current date (%date%).If so,do some job,otherwise quit (exit /b)

I found a bunch of solutions but what troubles me is date format.
On my machine it is dd.mm.yyyy

Thanks in advance.
 

Answer:Solved: batch to compare file modified date with current date

10 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'd like to be able to add a note to a memo field in a contact form for multiple contacts using either a query, search and replace or a rubber chicken with magic incantations. I don't really care how I get there. What I want to be able to do is, after sending a few dozen e-mails, update each record with exactly the same phrase noting that I did so.Search-and-replace works fine but doesn't seem to allow me to add in carriage returns/line feeds so the notation just looks like one long sentence.An update query also doesn't seem to work. When I search for an exact phrase, nothing shows up because the field has other content in it. When I search for that same phrase with wildcards the records show up but the entire field is selected and eliminated, replaced with the target, meaning I lose all my other notes in the same field. There must be a simple solution to this but I'm not able to find it. People don't manually update each record do they? The update query likewise places the carriage return/line feeds literally instead of as an actual change of line. Is VBA the answer?Brian Grover, AuthorBC Car-Free: Exploring Southwestern British Columbia without a Carwww.car-free.ca

Answer:Access 2007 Add notation to memo field in multiple records

You'll find the Solution Here: http://www.access-programmers.co.uk...Brian Grover, AuthorBC Car-Free: Exploring Southwestern British Columbia without a Carwww.car-free.ca

7 more replies
Relevance 69.7%

And yes, I did set the property to 'can grow' and 'can shrink'. I can't figure what the problem is. Even if I stretch the field out huge, it still is cut off when I view the report. And in one case, the same field prints fully when it is used on one report but on another similar report the same field is cut off. I can't see any differences between the two report settings.
 

Answer:Access 2007 Memo field not fully showing on report

8 more replies
Relevance 69.7%

I have a very simple DB set up. There are five columns: Project | Version | Link | Complete | Dependencies

My issue is with the Dependencies column. The dependencies column will list projects (see first column) needed by the project in that record. My issue: many of my Projects have four or five Dependencies! How do I enter those multiple values?

Endgame: I want to be able to print a report that shows me all the projects that are both A) Marked Complete B) Have all Dependencies marked Complete. Also, I want to print a list of complete projects that are being held up by dependencies.

Is there a way to do this in Access or am I going to have to try with a different program?
 

Answer:Solved: Access 2007: Multiple values in a single field

11 more replies
Relevance 69.7%

Hi,

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

I created a form letter as an Access 2007 report. The only field is [first_name] from a query. The first line reads, "Dear Joe (or whatever [first_name] is). I want a comma after each name. I tried =Trim([first_name] & ","), =Trim([first_name) & "," and every combination I can think of. Every permutation returns Dear #Type!. How can I get Dear followed by the name and a comma? Thanks!
 

Answer:Solved: Access 2007 Trim and field text addition

14 more replies
Relevance 69.7%

Am sort of new to the world of Access and VBA and am working on a DB. I am trying to take the selection from a combo box in one field and combine that to a "000X" number to create an incident # based on that selection that would populate in a field that is already set up in the same table. This number would increased as new incidents are entered into the DB.

An example would be if "Hang up" were chosen from the combo box the incident # field would then show Hang Up-0001 and increase the next time Hang up was chosen. Same thing if "Theft" was chosen etc.

Is something like this possible?

Thanks
Erica
 

Answer:Access 2007 VBA code to concatenate result from one field to incremental #

You may be looking at this in the wrong way. I would create a customer table or however you want to refer to them and a seperate incident table that will hold all of the information related to a given incident. You could also create a seperate table related to the incident table for troubleshooting steps or other items related to the given incident. The concatenation would probably be unnecessary if you lay out your tables correctly.
 

1 more replies
Relevance 69.7%

hi, i recently started using access 2007. i am not an avid programmer, but managed to create tables / reports / forms for a small database application at our office. an overview of the architecture would be - users enter the data (customers / leads) from their front ends, manager verifies and adds some more data. thats it. i have created a few reports to check the activity.

now i have an issue, of late my manager complained that the number of applications generated by his team was too high (100 a month) and he was scrolling through the form to check each record. if he had to go to the 52 record, he has to scroll through all the 52 records from the begining. can anyone suggest a simple design where in my manager can go to a record of his wish. i am not a s/w programmer,so i would be greatful if you guys can keep it simple.

thanks in advance.
 

More replies