Computer Support Forum

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

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

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

Relevance 100%
Preferred Solution: Solved: Access 2007 YES/NO field with time/date

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

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

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

Answer: Solved: Access 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 80.62%

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

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

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

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

from email address removed by Dreamboat

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

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

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

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

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

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

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

Answer:Solved: Extrapolate Month from an Access date field

16 more replies
Relevance 85.28%

Good day guys

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

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

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

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

BUT what I want is the following:

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

or even nicer

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

Can this be done in such a control?

Chris
 

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

7 more replies
Relevance 85.28%

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

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

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

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

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

This form updates an underlying table:
"Work_Hours"

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

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

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

I've created a table called:
"tbl_Pay_Period"

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

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

and set ... Read more

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

9 more replies
Relevance 82.82%

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

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

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

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

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

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

Answer:Solved: Update a time field with access vba

16 more replies
Relevance 81.18%

Now that I have the rate information pulling from my other table, the Rate Total won't calculate unless I overtype the rate field with a new entry. What I would like to do is just press enter to confirm the field and move past it and have the total automatically recalculate.

I think that this would be an event procedure on Enter but I don't know what VBA code to use to make it happen?

Can you please tell me if this is correct and what code will make this recalculate?
 

Answer:Solved: Access 2007, Recalculate field after data is pulled from table. OBP??

8 more replies
Relevance 80.77%

Running Access 2010. I am building a database that requires a time field and I would like to make it as simple as possible for the user to enter the time in standard 12 hour mode. For 6:00 am I would like the user to be able to enter 6a, for 12:35 pm I would like them to be able to enter 1235p, etc. No spaces or colons to enter. I cannot seem to devise a custom input mask to do this. I am not very proficient at VB but am willing to try with some clear direction. Any help will be greatly appreciated.
 

Answer:Solved: Access Input Mask for Time Field

You could use
99:99
but might have to use 99:99:99 as a short time format usually has seconds
6:00 am would be 06 as Access assumes values less than 12 are am, likewise 12.35 is pm as 35 minutes past midnight is 00:35.
So in the table and form set the
Format to Short Time
Input mask to 99:99
and the
Default Value to 00:00
 

3 more replies
Relevance 80.36%

I have a form with a subform. When I enter the Employee number in the appropriate field, the subform updates with the employee's information. I then want my form (not subform) to automatically populate in another field from the subform that has the individual employee's information. How can this be done. I have racked my brain and I know there has to be a way. OBP, are you out there?
 

Answer:Solved: Automatically populate info into field after subform updates, Access 2007, OB

16 more replies
Relevance 80.36%

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

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

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

On Error GoTo Handle_err

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

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

... Read more

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

16 more replies
Relevance 77.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%

Hi,

I believe this is a really easy thing to do but i can not get it to work,

I have a table that contains a name and the dates that they are on holiday. i am wanting to have a query that will show me everyone who is on holiday in February.

I thought this was simple but i have not been able to get it to work, i think i am missing something really really simple.

I have attached a copy of the table, the table is part of a college project and all data is fictional.

The attachment is zipped so it could be attached

Thanks
James
 

Answer:Solved: Access 2007: date Query

James, to find all dates for February can be done in 2 ways, you can use from the 1st (startdate) to the 29th or 28th (eneddate) and then type in the Query Criteria Row
between 01/02/2008 and 29/02/2008
or you could use the Date part or date Format to extract February form you date and then compare it to February, but that only works if you have one years worth of data.
I can't help with your zipped file as I do bnot have Access 2007.
 

2 more replies
Relevance 77.9%

Hi,
My database contains date fields that indicate when a particular action needs to be executed (e.g. send a follow up letter to the sales call).
Can Access 2007 be configured to alert me when that date arrives?
Thank you.
Tim
 

Answer:Solved: Access 2007. Can it alert me when a date is due?

16 more replies
Relevance 77.49%

Hi everybody

I have got an hourly meteorological data for 10 years. The problem is that some of the data entries are missing. e.g.

23-April-2006 19:00
23-April-2006 20:00
23-April-2006 22:00
23-April-2006 23:00

I need to insert the rows for missing hours. Rest of the columns in the inserted row are needed to be blank. e.g.

23-April-2006 19:00
23-April-2006 20:00
23-April-2006 21:00
23-April-2006 22:00
23-April-2006 23:00

Please also note that for some years, the data entries are for half past hours. e.g.

31-Dec-2001 23:30
01-Jan-2002 00:30
01-Jan-2002 01:30
01-Jan-2002 02:30

Peace
Muhammad Zeeshan
 

Answer:Solved: Inserting rows for Missing Date/ Time data (Excel 2007)

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

Hi all.

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

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

>Date()-30

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

any ideas?
 

Answer:Solved: Access 2007 Date Query Problem.

7 more replies
Relevance 77.08%

Hi Everyone,
I am trying to create a query that selects all records with a future date. The table that I am trying to select the records from is named "Events" and the field containing the date is "Date"
I am using Access 2007 and the date field is formated as 'Date/Time' data. I tried multiples ways, and even tried the 1000 year add date function pasted in below. Does anyone have any insights--I'm sure its something that is quite simple.

Thanks,
Joe

SELECT Events.Date, Events.Event
FROM Events
WHERE (((Events.Date)=DateAdd("yyyy",1000,Now())));
 

Answer:Solved: Access 2007 Future Date Query

6 more replies
Relevance 76.26%

I have an access report that I would like users to be able to enter a start and end date. The report will be filtered to display data that falls between the two dates.

I have already created a form with date fields. I'm having a hard time figuring out how to use the form to filter the report. I also would like the report to display all data if teh user decides not to enter any dates.

How can this be done?

Any help will be greatly appreciated.
 

Answer:Solved: Access 2007/Filter Report By Date Range

There are many ways to do it, but I would set the filter in the OpenReport method when the report is launched, and provide logic to either set the start and end dates to extreme values (like 1/1/1904 thru 1/1/2999), which programming purists would tell you to never do, or not set the filter at all if the user doesn't enter a date.

In the code for the button that launches the report:

dtStart = Nz(Me.StartDate,#1/1/1904#)
dtEnd = Nz(Me.EndDate,#1/1/2999#)
DoCmd.OpenReport "Report1", acViewReport, "", "[MyDate]>=#" & dtStart & "# And [MyDate]<= #" _
& dtEnd & "#", acNormal

--- OR ---

If Isnull(Me.StartDate) or Isnull(Me.EndDate) then
DoCmd.OpenReport "Report1", acViewReport, "", , acNormal
Else
DoCmd.OpenReport "Report1", acViewReport, "", "[MyDate]>=#" & Me.StartDate & "# And [MyDate]<= #" & Me.EndDate & "#", acNormal
End If

The tricky part is getting tripped up by not including the #, designating a date literal.

WearerOfManyHats
 

2 more replies
Relevance 76.26%

I am trying to write a function that will determine the first day of our fiscal year for whatever date is inputed into it.

Our fiscal year always ends on the friday closest to 1/31.

I'm having trouble figuring out how to deal with january dates.

Any help would be greatly appreciated.
Code:

Function FiscalYearStart(dteDate As Date)
Dim dteNextFriday As Date
Dim dteLastFriday As Date
Dim x As Integer
Dim dteJanThirtyFirst As Date

'set as january thirty first of calendar year
dteJanThirtyFirst = "1/31/" & Format(dteDate, "yyyy")

'if 1/31 is a friday
If Weekday(dteJanThirtyFirst) = 6 Then

'use 1/31 and exit function
FiscalYearStart = dteJanThirtyFirst
Exit Function

Else: End If

'set as friday after january thirty first
dteNextFriday = DateAdd("d", 8 - Weekday(dteJanThirtyFirst, vbFriday), dteJanThirtyFirst)

'determine day of week for january thirty first
x = Weekday(dteJanThirtyFirst)

'if weekday is saturday
If x = 7 Then

'subtract 1
dteLastFriday = DateAdd("d", -1, dteJanThirtyFirst)

Else

'subtract 1 and the number of the weekday
dteLastFriday = DateAdd("d", -1 - x, dteJanThirtyFirst)

End If

'if last friday is less than next friday
If DateDiff("d", dteJanThirtyFirst, dteNextFriday) > DateDiff("d", dteLastFriday, dteJanThirtyFirst) Then

'use last friday and add 1 day to get start of fiscal year
FiscalYearStart = DateAdd("d&quo... Read more

Answer:Solved: Access 2007 VBA Fiscal Year Start Date

16 more replies
Relevance 75.85%

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

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

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

2 years, 1 month & 6 days

but certainly not:

2 years, 25 months & 761 days

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

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

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

Answer:Solved: Access: Elapsed Time Since Specified Date

16 more replies
Relevance 75.85%

Hello everyone,

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

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

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

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

Any ideas?

Thanks.
 

Answer:MS Access - autofill date field

6 more replies
Relevance 75.03%

Ok, here is what I have.

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

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

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

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

Thanx in advance.
 

Answer:Solved: Access - Closed Date/Time Stamp

13 more replies
Relevance 75.03%

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

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

Answer:MS Access Concatenate a date and text field

Jerry-

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

19 more replies
Relevance 74.21%

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

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

11 more replies
Relevance 74.21%

Hello guys,

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

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

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

TotalOverPeriod - 2 Days, 1 Hour, 50 Mintues

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

Thank you
 

Answer:Solved: Access 2010 Total Time and Date Values

16 more replies
Relevance 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%

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

More replies
Relevance 74.21%

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

George
 

More replies
Relevance 74.21%

Hello

Access 2003
Windows XP

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

Thanks

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

Answer:Access 2003 - Format field to Short Date

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

3 more replies
Relevance 73.8%

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

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

Thanks for any help you can provide.
 

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

15 more replies
Relevance 73.39%

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

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

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

Arrival Date
Arrival Time
Exit Time

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

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

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

As wagons could be on site for multiple days (so the 1440 mins figure could be wrong) and could leave x amount of days plus x amount of minutes after arriving (so the assumption that it's exited the same day as it arrived could be wrong),... Read more

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

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

Hiya!

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

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

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

Many thanks!
 

Answer:Solved: Access 2007: Validation Rule for Time

9 more replies
Relevance 71.34%

Hi there,

Can you give me some idea on how to do this...

I am creating a Training Management solution. For this some of the training modules they need requalification after x time. (This time is stored in the database, but is currently stored as (6 months, annually or 3 years) (should this be stored as the number of days?).

So the training table looks like this:

TrainingCode - Primary Key
Traning Name
Requires refresher? - List (6 months, annually, 3 years)
Mandatory? - Yes/No

And I have another table which links a colleague to a training module with a date they trained which looks like this:

ID - Primary Key
Training Name - List from Traning table
Colleague Name - List from Colleagues table
Date Trained
Trainer - List from Colleagues Table

Nowww... what I can't get my head around is:

a) How to create a report to show a list of all colleagues that are overdue requalification training

b) How to create a report to show a list of all colleagues that are due refresher training in the next 60 days

c) How to create the queries to run both of the above reports

d) How to print a list of all mandatory training that is incomplete...
That is a list of all colleagues mandatory training for what there is no record in the ColleagueTraining table.. I already have a list of all mandatory training linked to a job role in the ManTraining and Colleagues tables respectivley.

In addition, I need to be able to limit the details put into the table by the following factors:
- Colle... Read more

Answer:Solved: Access 2007 - time related query

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

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%

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%

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

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

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%

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%

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

I don’t know VBA but have been using Access for awhile using tables, queries, forms and macros. I am trying to automate importing a number of Excel 2007 spreadsheets into an Access 2007 table. The import will be done each month, but the number of spreadhseets could be different. The spreadsheets will all be saved to the same folder. I need to import all the spreadsheets in the folder into the same table. I found VBA code that was written for Excel 2003 and Access 2003. I changed the path, the table name and the spreadsheet type. When I run the code I get Run-time error 52, Bad file name or number.

When I debug the code, it highlights the line this line --strFile = Dir(path & "*.xlsx").

Any help will be greatly appreciated!
Code:

[COLOR=black][FONT=Arial][COLOR=black][FONT=Arial][FONT=Calibri][SIZE=3]Function getData()[/SIZE][/FONT][/FONT][/COLOR][FONT=Arial]
[COLOR=black][FONT=Calibri][SIZE=3]Dim strFile As String 'Filename[/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Calibri][SIZE=3]Dim strFileList() As String 'File Array[/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Calibri][SIZE=3]Dim intFile As Integer 'File Number[/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Calibri][SIZE=3]Dim filename As String[/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Calibri][SIZE=3]Dim path As String[/SIZE][/FONT][/COLOR]

[COLOR=black][SIZE=3][FONT=Calibri] DoCmd.SetWarnings False[/FONT][/SIZE][/COLOR]

[COLOR=black][SIZE=3][FONT=Calibri] path = "\\c:\Monthly Data\"[/FONT][/SI... Read more

Answer:Solved: Debugging for Access 2007 VBA Run-time Error 52 - Bad file name or number.

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%

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%

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%

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

Answer:Solved: Stop Updating the Date Field

6 more replies
Relevance 69.7%

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

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

thanks in advance
 

Answer:Solved: excel length of date field

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

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

exit celll with ctrl, shift, enter
 

1 more replies
Relevance 69.29%

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

me.requery
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 69.29%

Hey everyone,

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

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

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

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

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

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

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

8 more replies
Relevance 68.47%

Hi All,

First post.

i need to see the reply date time stamp for each email in a inbox is it possible to add it has a cutom column or maybe vba etc.


really could do with some help on this asap.

Cheers

Karl.
 

Answer:Reply Date/Time Outlook 2007.

yeah its possible you need to change the email settings and then you can able to get the time and date as well. Outlook PST Repair
 

3 more replies
Relevance 68.47%

My DAQ system outputs the time and date for each data point collected in the first column along with the various signals in other columns, and I have at least 4k data points in each column. Time and data is given as shown below.11:54:23:11 - 23 MAY 1011:54:38:11 - 23 MAY 10I am trying to extract the time diference between the two data points by subracting the info in two cells, and obviously Excel does not recognize the info in the cell because of the presence of ":11" after hh:mm:ss info.How would I delete ":11" from the column and convert the given info to a Excel readable time/date format so that info in two cells could be subtracted to get the time difference.Thanks

Answer:Time and Date Format in Excel 2007

Are the Time & Date together in one cell?If the times are in there own cells, then Custom Format as:[h]:mm:ss.00This will allow you to keep the full time.MIKEhttp://www.skeptic.com/

7 more replies
Relevance 68.47%

Good afternoon all!

I am working on writing a Security Incident Report program, and I am running into a problem. I have a Date/Time field, which I am attempting to auto-update from another Date/Time field, based on the result of a Yes/No field input. Is this even possible? To be specific, the user enters the date of the report. On the next form page, it asks the user if the report date is the same as the incident date. If the user answers Yes, I would like it to enter the data from the report date field and grey out the option to enter the new date. If the report date is different from the incident date, the user would then be required to enter the date of the incident. This takes place three different times on the same form, based on the incident date, date responders cleared from the incident, and the date the incident was placed under control. I am running Access 2007. Thank you!
 

Answer:Creating Access 2007 Auto-populate field based on different table Yes/No data

GrahamTechnology, welcome to the Forum.
Yes it is using VBA.
 

3 more replies
Relevance 67.65%

Hi,

All of a sudden my Access 2007's Expression Builder just stopped showing the fields names for reports, queries, tables, etc. This was working one day and just stopped. I wonder if I may have flushed an add-in or something. Help!
 

Answer:Access 2007 Expression Builder not showing field names for tables, reports ot anythin

I have had some pretty squirrely issues with the expresion builder also. I like to use it as a general guideline but usually just type in all of my expressions by hand. Are you linking to external database(s)? I had a problem like the one you are experiencing but it was a few years ago and I'm not certain about how I got around it.
 

1 more replies
Relevance 67.65%

I am using MS Word 2007 - I use on a daily basis the track changes function - however, I am not (as used to be the case with MS Word 2003) able to see the Date/Time Stamp and the Reviewer's name when I drag my cursor onto the relevant "tracked" change (i.e. the insertion or the deletion). I have tried to contact Microsoft, but they want to charge me a fee equal to the purchase price of the Office Suite just to answer a simple question in respect of their own product. Can anyone please help me?
 

More replies
Relevance 67.65%

I have Office 2007 running on a Windows 7 64 bit Toshiba laptop. I am trying to separate the data and time in an Excel file in one column and put the date in one column and the time in another. I need a "how to" procedure to do that.

Answer:Separating date and time in Excel Office 2007

Excel has formatting for Date and Time.
For column A use a Date format, and for column B use a Time format.
To copy the value from column A to column B a function in column B like this should work
= IF(A1="","",A1)

5 more replies
Relevance 67.65%

I have pasted data in to Excel that is in two columns
Col A contains a list of dates, dd/mm/yy; col B times hh/mm/yy. How do I set about calculating and displaying the period in dd/hh/mm/ss that elapsed between col A row B with col B row B, and col A row C with col B row C ?

Answer:Excel 2007: a question about date and time manipulation

Post a picture of a sample worksheet containing sample data so I can fully understand what you need to do.

I will replicate the picture you post and can probably figure it out.

As I understand it, column A contains only something like 13/6/2007 (June 13, 2007). Column B contains hours I guess, but I need a sample to actually look at and ponder.

You want to calculate the total time elapsed between some time on June 13, 2007 in column A and some later time as shown in column B??

Column A contains no reference to hours? Hours are found only in column B?

More info please.

4 more replies
Relevance 66.83%

Hello Everyone!

I have a field that has Days (Ex. Monday, Tuesday etc...)

Is there a way to turn this Days into a nearest date coming up. So for example if i have Wednesday on the field as a value and today is tuesday... I want the date for tomorrow displayed.

I have been doing research but I cant find anything. I have found the opposite but it doesnt do me any good. Thank you very much.
 

Answer:Access 2007 Day To Date Help

16 more replies
Relevance 66.42%

Hello,

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

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

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

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

Thanks

Daniel
 

Answer:Exported date field not recognized as date in Excel

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

3 more replies
Relevance 66.42%

I accidentally formatted the date cell & it only accept numbers

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

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

2 more replies
Relevance 66.01%

yo...

im a student in intern programe right now. my organization give me a ms access file that contain information of contract workers. they ask me to use access and make a pop up to tell the user 3 months before contract expired. what make im more crazy when she ask me to make due date field become bling bling or shiny to make us easy to check. im not familiar with access and i never use it before. what i was google before and i dont get the answer that i want.

i already post the same question on other web and I hope I got what I want.

please i need help i dont care who you are and i really want to say terima kasih (thankyou)
 

Answer:Ms Access 2007 Due Date Pop up Tutorial

16 more replies
Relevance 66.01%

Hi,

I have a form with a listbox on it which displays all the data in one of my tables. I also have a combo box which displays a list of dates (short date) from the table.

I'm trying to query the table based the combobox to get all the records that came after that date.

However, at the moment, when i enter a date, and the list is requeried, the data is not correct.

For example, there is data for everyday (from 12/10/2010 to 26/10/2010) so if i select 12/10/2010 from the combo box, id expect to see records from 13/10 to 26/10.

what i actually get is some records starting at 24/10.

If i change the query so that it looks for records before the selected date, nothing is returned.
Any suggestions as to whats going on?
Cheers
Lewis
 

Answer:Access 2007 Date Filter

16 more replies
Relevance 66.01%

I am new to using Access 2007. Please help if possible. I have an access 2007 query and I need to sort it by dates. I have 3 field dates... need to sort each field if not null.

I need for it to sort by field1 date first, and it field1 date is null, sort by field2, and if field2 date is null, sort on field3.

I hope I explain it good enough...I am a rookie.
 

Answer:Access 2007 Query Date

Hi and welcome to the forums

Please only make one thread per issue, as having duplicates will confuse things . I've removed your old thread and moved this one to a section that may get some more answers.
 

3 more replies
Relevance 66.01%

Hi all, OBP has helped greatly with my access issues but ive stumbled accross another which i just dont understand.

I have a form with several textboxes on and two buttons.

The user enteres search criteria into the boxes and then each button performs a different query.

One of the textboxes is for a date. with one query it works fine, you can enter a date and it brings back the expected results, with the other, i get nothing whatsoever.

In the working query, the date is queried using :
Like "*" & [Forms]![frm_Search_CertificateOfConformity]![Date Scanned] & "*"

In the other query:
Like [Forms]![frm_Search_CertificateOfConformity]![Date Scanned]
I really dont understand why it doesnt work.
Any assisstance will be appreciated.

Cheers
Lewis
 

Answer:Date search in Access 2007

10 more replies