Computer Support Forum

Solved: Extrapolate Month from an Access date field

Question: Solved: Extrapolate Month from an Access date field

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.

Relevance 100%
Preferred Solution: Solved: Extrapolate Month from an Access date field

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: Extrapolate Month from an Access date field

16 more replies
Relevance 86.1%

I have a field that is date/time, formatted to display the long date (which starts with the day of the week). This has been extremely useful for some append, update and delete queries.

However, I would like to be able to do an update query based on the day of the week for each record. I know that the date/time field is stored as a number so I can't search for any string of letters. But, is there a way to take the long date format and copy what's there as a string so I can then extract the day of the week out of it? Otherwise, I guess I'm stuck with adding a separate field for day of the week, and manually inputting it. Seems like such a waste.
 

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

7 more replies
Relevance 86.1%

The company I work for has asked me to work with the DB (as you may well guess in this economy I now have to learn how to work with Access DB. This DB was created by someone who had a book “Access 2000 for dummies” in 2002, I have since then converted the 2000 DB to 2007 accdb format. That being said, in a form that is used to track records-TS# is the primary key, they want a YES/NO or actual name- Completed *check box for yes and blank for no*. I created the check box as you may know that was easy enough. The problem comes in when I go to the next record the box remains in whatever state it was on the previous record and does not show up anywhere else. I am sure this is likely because there are steps that I am omitting or not aware of. Any help offered would be amazing as I continue to work on this train wreck.

-Mike
 

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

Mike, the Check Box field has to be in the Table that the Form uses as it's Record Source. The Check box has to be "Bound" to that table's field, i.e. it's "Contorl Source" of the check box in design view must be the table field.
 

2 more replies
Relevance 85.28%

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

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

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

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

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

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

Can anyone help me?
 

Answer:Solved: Access 2007 Record Date When Field Updated

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

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

How can I make

Date______ Premium
1/1/2007 _______1
1/5/2007 _______3
2/1/2007 _______1
3/1/2007 _______0
1/1/2008 _______5

Look like.................

Year _Month _Total Premium
2007___ 1______ 4
2007___ 2______ 1
2007 ___3______ 0
2008___ 1______ 5


in Access 2007? Please help!
 

Answer:ACCESS group by date, month, and sum value.

You could use a GROUP BY query grouping by Month(<your date field>) and Year(<your date field>), and summing by <your premium field>
 

5 more replies
Relevance 75.85%

I am trying to display ages in both year and month..but I can't seem to figure out how to do this. Any help?
 

Answer:Access: Date calculations - Need Yr and Month

8 more replies
Relevance 75.85%

Hello everyone,

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

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

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

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

Any ideas?

Thanks.
 

Answer:MS Access - autofill date field

6 more replies
Relevance 75.03%

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

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

Answer:MS Access Concatenate a date and text field

Jerry-

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

19 more replies
Relevance 74.21%

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

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

George
 

More replies
Relevance 74.21%

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

More replies
Relevance 74.21%

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

Access 2007 Update Query For Updating A Date Field

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

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

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

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

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

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

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

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

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

I would like to update the ProjectDate in an update query

Any suggestions? Thanks
 

Answer:Access 2007 Update Query For Updating A Date Field

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

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

3 more replies
Relevance 72.57%

I would like to "Build" an expression for a date/time field that will query the date and display my query output as "01/01" or "January, 1st", and leave off (omit) the year.

Im trying to print a report of my employees birthdates without displaying the year, so i can post it publicly.

the name of the column is dob (date of birth) and the format is standard date/time, displayed normally as 01/01/2009 etc ....

The best idea i could come up with so far is

= Format (Date(), "dddd, mmm d")

but this displays todays date and not the date stored in my column lol.
 

Answer:Access 2003, want date/time to show day and month only (& no year) in query

7 more replies
Relevance 72.57%

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

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

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

What code would accomplish this?

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

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

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

More replies
Relevance 72.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 70.52%

Hi,

I have a column containing set of dates; i need to get the dates exactly after two months to appear in next column automatically. e.g, I have cell with date 6-Feb-09, I need 6-Apr-09 to come in next cell automatically. Which formula can I use it?
 

Answer:Solved: 2 month later date in next column

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

Encountered a small but irritating problem between Access 2000 and Access 2003. Designed a table and input form in Access 2000 that contain a date field to capture credit card expiration dates, which are always only month and year. Made the format "m/yy" with an input mask of #0/00;0;_. Works perfectly in my Access 2000, but when I installed the application on a client's (non-profit org.) computers, which have Access 2003, every entry of a year other than this year (06) gets changed to 06. E.g., if the user enters 0508 and tabs to the next field, it displays "5/06". It behaves as though it thinks you entered only the month and DAY, but since the format has to be m/yy, it displays the 5 and tacks on the current year.

Access (at least Access 2000) does not offer a built-in month/year format. So how can I make the field understand that what goes in there is supposed to be only month and year? Is it possible to do with a Date/Time field type, or do I need to make it Text, or what?
 

Answer:Solved: Month/Year date format

i'm a surprised on the M/YY format - how doesOct Nov Dec work 10/11/12
should the format be MM/YY
 

3 more replies
Relevance 69.7%

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

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

under the format options.

5 more replies
Relevance 69.7%

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

Answer:Solved: Stop Updating the Date Field

6 more replies
Relevance 69.7%

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

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

thanks in advance
 

Answer:Solved: excel length of date field

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

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

exit celll with ctrl, shift, enter
 

1 more replies
Relevance 69.29%

Hey everyone,

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

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

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

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

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

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

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

8 more replies
Relevance 68.47%

I need to set up a query that pulls information matching a certain date of the current month and year. I'm not sure about the syntax. Someone help please?
 

Answer:Solved: Query for specific date, current month and year.

16 more replies
Relevance 68.47%

My 2001 Northgate Intel Pentium 4, 1.7GHz running Windows XP no longer advances automatically. Is there a battery that is inexpensive to revive this feature? Also, is this a fairly easy replacement?
 

Answer:Solved: Date & Month, & Time no longer advance automatically

6 more replies
Relevance 67.24%

I exported a list of invoices to excel from my accounting program. One of the fields, of course, is a date field mm/dd/yyyy.

I am trying to figure out how I can sort, pivot, something with the goal of totaling by month.

I've tried formatting the column to mm-yyyy and text-to-columns to isolate the month and it's just not working right.

Suggestions?

Thanks
 

Answer:Solved: Extracting or Focusing on the Month part of a Date for a Pivot Table? Excel

6 more replies
Relevance 66.42%

I accidentally formatted the date cell & it only accept numbers

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

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

2 more replies
Relevance 66.42%

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

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

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

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

(see attachments that show the above)

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

Does anyone have any better ideas?

Jim Shannon
JES Computer Systems
Access Developer.
 

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

7 more replies
Relevance 64.37%

I have a Date/Time field that I need converted to just a Time field.

It comes back with 2/11/12 0:00:00

I need just the 2/11/12

I thought about changing it to a text field but I think there will be an issue with the month because it does not bring back a 0 in from the months with only 1 digit.

This table that I am creating is being sent to a txt file and that is where I see the issue. It looks good in Access, but not the txt file.

Any suggestions?
 

Answer:Solved: Access - Convert Date Time to Date

10 more replies
Relevance 63.55%

I create demo files for testing. The way I have it now I have the file laid out with two merge fields. These link back to another docucment where I input a month's worth of dates. The first being Date created and the second column is File Load date.

Once this DateInput doc is updated I go through each record in the merge document and save each one as the DateCreated number as the file name and as a txt file.

Any ideas on how to automate this? I tried using Access to fill in the merge and I was able to have it input today and tomorrow dates using command buttons and these subs

Private Sub cmdToday_Click()
txtFrom = Date
txtTo = DateAdd("d", -1, Date)
End Sub

Private Sub cmdTomorrow_Click()
txtFrom = DateAdd("d", 1, Date)
txtTo = Date
End Sub

But that only gets me two days, I need to see about automating the whole month.

Any help would be appreciated. Thanks
 

Answer:Whole month of text files with merge field

Any ideas on how to do this? I have tried everything that I know. I tried making a macro in Word, but could not get that to work either.
 

1 more replies
Relevance 63.14%

DATE TEST
Month Total
2015/04 1
2015/07 1
2015/09 1
2016/01 1
2016/02 1

As you can see from the above results an Access query there are periods without any data. I need them to be displayed within the query as a zero. Can someone tell me how to achieve this easily please.

My query currently reads:

SELECT Format([TimeOfCall],"yyyy/mm") AS [Month], Count([INCIDENT NEW].SALESIncidentIdentifier) AS Total
FROM (SALES LEFT JOIN VICTIM ON SALES.IncidentIdentifier = VICTIM.SALESIncidentIdentifier) RIGHT JOIN [INCIDENT NEW] ON SALES.IncidentIdentifier = [INCIDENT NEW].SALESIncidentIdentifier
WHERE (((VICTIM.VictimType)="1"))
GROUP BY Format([TimeOfCall],"yyyy/mm");
 

More replies
Relevance 62.32%

Hello,,,

I ran into a problem with our financial month end.

I need a Q that displays Totals between 21th and the 20th of each month. How do you create a Q that displays something like this:

[Period]_________________[Amount for the Period]
21/01/2010 - 20/02/2010___$ XXXXXXX.XX
21/02/2010 - 20/03/2010___$ XXXXXXX.XX
21/03/2010 - 20/04/2010___$ XXXXXXX.XX
...
How its (not)working now
In Access I used the MonthName(Month([Period])) function this display data in normal month order which is wrong like so:

[Period]_ [Amount for the Period]
Jan_____$ XXXXXXX.XX
Feb_____$ XXXXXXX.XX
Mar_____$ XXXXXXX.XX
...

Any ideas would be appreciated!
 

Answer:Solved: Access Financial Month End Q

12 more replies
Relevance 61.91%

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

Any Ideas????
Please
STUART
 

Answer:Solved: Access NO editing in a certain field

Please can somebody help me
 

3 more replies
Relevance 61.91%

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

Answer:Solved: MS Access Attachment Field

6 more replies
Relevance 61.91%

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

Answer:Solved: Access: ID from calculated field

10 more replies
Relevance 61.91%

Hello,

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

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

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

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

Dim rsOntime, rsData As Recordset
Dim MVar As String

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

' Loop for metrics
For k = 1 To 24

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

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

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

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

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

6 more replies
Relevance 61.91%

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

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

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

Thx
 

Answer:Solved: Access Query Y/N Field

7 more replies
Relevance 61.91%

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

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

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

Answer:Solved: Access Search Field

11 more replies
Relevance 61.5%

Hello,

I'm currently using the following:

Microsoft Windows 7 Enterprise Operating System
Microsoft Office 2010

I have an Sql query in Access 2010 that produces a two column result:
Unfortunately I'm having problems trying to sort the data by the Month column from oldest to newest.
In a previous query I used the Format(Month,"mmm-yy") to set the Month display below.

Month Qty
Apr-13 5
Aug-13 5
Dec-12 4
Dec-13 3
Feb-14 1
Jan-13 1
Jul-12 1
Jul-13 10
Please can anyone help?.

Many thanks.
 

Answer:Solved: Sorting Month column Access

14 more replies
Relevance 61.5%

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

Here is what I have..

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

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

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

So what I want to end up with is just:

testing.xls
testing.doc

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

Thank you in advance!

Edit:

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

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

Answer:Solved: Access - Removing Data in Field

9 more replies
Relevance 61.5%

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

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

Answer:Solved: Access Subform - Memo Field

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

2 more replies
Relevance 61.5%

Hi all,

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

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

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

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

Answer:Solved: Adding field to Access Form

10 more replies
Relevance 61.5%

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

Answer:Solved: Update a time field with access vba

16 more replies
Relevance 61.5%

Hi all,

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

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

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

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

6 more replies
Relevance 61.5%

Good day,

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

Scenario No. 1:

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

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

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

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

Thanks for your assistance.
 

Answer:Solved: Access 2010 MVL field Errors

16 more replies
Relevance 61.5%

Hi,

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

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

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

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

EDIT

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

Answer:Solved: Access 2007 Field Lookup

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

1 more replies
Relevance 61.5%

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

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

NICUTotal (text box)
NICU Total (label box)

And

OBTotal (text box)
OB Total (label box)

Any help you can give would be greatly appreciated!!
 

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

8 more replies
Relevance 61.5%

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

Thanks
 

Answer:Solved: Access Manipulate data in a field

7 more replies
Relevance 61.5%

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

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

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

The form uses a "Close Form" button.

thanks for any help.
 

Answer:Solved: Mandatory field in Access 2010

16 more replies
Relevance 61.5%

Hello All,

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

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

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

Thanks in advance for any and all assistance.

Scooter1959
 

Answer:Solved: Required Field in Access Form

16 more replies
Relevance 61.5%

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

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

Answer:Solved: Microsoft Access Increment a field

16 more replies
Relevance 61.5%

Hello,

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

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

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

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

Answer:Solved: Append Message To Field In Access

15 more replies
Relevance 61.5%

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

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

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

Thanks in Advance

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

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

6 more replies
Relevance 61.5%

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

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

End If
End Function

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

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

Answer:Solved: Access 2k field change verification

13 more replies
Relevance 61.5%

Hi Everyone!

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

Mouse, Mickey

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

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

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

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

2 more replies
Relevance 60.68%

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

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

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

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

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

8 more replies
Relevance 60.68%

I have a field in my Access 2003 database that currently requires the user to enter the 2 digit month & 2 digit year of the incident as the prefix (prefix) to the case number. I would like to have this automatically created based on the date entered in the date of incident field (date).

Any ideas would be appreciated.
 

Answer:Solved: Access 2003 automatic field entry

16 more replies
Relevance 60.68%

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

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

On Error Resume Next

Dim strName As String

strName = StrConv(Me.Name, vbProperCase)

Me.Name = strName

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

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

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

15 more replies
Relevance 60.68%

Does anyone know the best way to copy records from one field in a table to another field in the same table? There are approx. 40,000 records that need to be copied.
 

Answer:Solved: Access Table: Copy Data from One Field To Another

16 more replies
Relevance 60.68%

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

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

Hi! This is probably a pretty basic question, but I can't find an answer anywhere here or on the net. How do I add new fields, using VBA, to an existing table in Access? Without destroying thd data? My process: I initially import, using VBA, an Excel spreadsheet into a new table (this table is replaced each month by a new spreadsheet.) To facilitate my analysis of the data contained in that table, I need to add several fields to that existing table. Those fields will be used to store the results of several automated processes using that data. Because the table is created from the spreadsheet provided by another department each month, I need to add the new fields each time I import the spreadsheet. I am trying to do everything in VB because the final user needs to perform all functions by merely pushing buttons on a &quot;Switchboard&quot; My version is MSAccess2003. Thanks for your help!
 

Answer:Solved: Adding a new Field to an existing table in Access using VB

12 more replies
Relevance 60.68%

Okay I hope this will be easy.

I have a form that links to a Table. We input information into this form it then dumps the information into the table. Lets call this table Table 1. We also have a second table that has all of our facilities information (I.E. Site name, location, phone number, and whatnot). Lets call this table Table 2.

Now on Table 1 there is a field that has a listbox of the Site Names you click on it get the drop down and pick the location you want. What it DOESN'T do is the following.

I have a phone number field in Table 2 that I want to auto-populate the phone number field of Table 1 depending on the site picked in the listbox where this listbox reffences the same table 2 I want the phone number from.

EG Table 2 has Site A in the first column and hone number A in the second column, Site B phone number B, Site C and phone number C and so fourth. I want to pick on Table 1 the Site A and have Phone number A auto populate from Table 2's Phone number A and so fourth.

Can this be done fairly easily?
 

Answer:Solved: Access DB -Autopopulate a field from a secondary table.

15 more replies
Relevance 60.68%

In my database I have what I call the main form. This form , among other things, contains a persons social security number [SSN]. I have placed a control button on this form that allows for me to jump to a hyperlink and perform a search of a second database through ASP. Now, everything is working fine but what I would like to do is be able to "plug in", via code, the [SSN] field data into the static portions of the hyperlink string. The SSN would have to be taken from the currently viewed record on the main form. The code for the button, as shown below, has a dummy SSN of 999-99-9999 (in orange) and this is the data that I need inserted from the [SSN] on the main form.
_____________________

Private Sub Command215_Click()
FollowHyperlink "http://1.1.1.111/Prog/ProgWeb.aspx?Action=Search&Database=database name&SearchType=DocumentProfile&SearchValue=999-99-9999&ProfileFieldName=Document%20Title"

End Sub
___________________________________________________
As the code stands, it works as long as I manually place an SSN in there. Any suggestions that will allow me to take the SSN from the currently viewed record in the main form and place it between the two static portions of the hyperlink?

Thanks in advance.
 

Answer:Solved: Access: Insert field data into hyperlink

7 more replies
Relevance 60.68%

I have a field [name] that can have up to 6 words within the field ex |hello my name is Miranda|
i am trying to get a query to split into different fields by the space between the words to look like:

|Hello| |MY| |NAME| |IS| |MIRANDA|

Any Ideas i would really appreciate it!?
 

Answer:Solved: Access: spliting a field into multiple through a query

11 more replies
Relevance 60.68%

I have a comment field that sometimes has comma's in them. The issue is that when we change this table to a .csv file the comma's cause issues. I have tried to Replace - but I am having a hard time with it.

The query that puts this field to the table currently has this code.

AuditorComments: Last((IIf([DisplayDatabase]![Add Comments for Display]="0"," ",[DisplayDatabase]![Add Comments for Display])))
How do I either remove comma's ore replace them with something like a dash?
 

Answer:Solved: Access - remove comma's in a Comment field

does this help
http://www.techonthenet.com/access/functions/string/replace.php
 

3 more replies
Relevance 60.68%

Afternoon all,

Let me explain what I am attempting to do here:

I have a database with a list of contacts, and with these list of contacts, I have also created an agenda. Inside this agenda table, I have a field called "Contact ID". "Contact ID" has a one-to-many relationship set up with the primary key in the contacts table, "ID". (refer to pic1.jpg) I intend for the database to automatically update the "Contact ID" based on what contact is currently selected.

For example, If I open a contact with ID #3 (refer to pic2.jpg), and then I open the Agenda form, the "Contact ID" will automatically update to the contact "ID". From here, I can add events and such that will automatically relate to that particular client.

My problem is I don't know how I should do this, whether I need to set a default value for "Contact ID", use a query, or use some sort of VB coding to get it done. I've already tried to use things like:
Code:
Private Form_Load()

[AGENDA].[Contact ID] = [CONTACTS].[ID]
and that didn't work. I assume I might need something like a TempVar or ActiveForm/ActiveDatasheet code to call for ID to be automatically updated?

Thanks for your help everyone, looking forward to your responses!
 

Answer:Solved: MS Access: Automatically determine the value of one field from an ID autonumb

16 more replies
Relevance 60.68%

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

I have a form that the user enters in the month and year.
text box 1 - month
text box 2 - year

I then run queries grabbing MTD - YTD data. I use these against an invoice date and have the field look like this - Month: Month([InvoiceDate]) with criteria [Forms]![Form1]![month].

The same concept for year.

This works great for for current year data but when I use it for prior year I have issues. I can use these and have the year be criteria [Forms]![Form1]![Year]-1..... works like a charm.

But when I try to grab Prior Year YTD data - so jan - april 2011 I use the same year criteria
[Forms]![Form1]![Year]-1

and I use the month criteria of <=[Forms]![Form1]![month]

And it returns back also 10,11,12 months.

I tried changing the Form textbox to Standard with no decimals but it still brings those back. How do I get it to on grab 1,2,3 months and not grab 10,11,12?
 

Answer:Solved: Access use Form Text Box to grab month for query

Did you try using a quarter (i.e. Quarter: DatePart("q",[EnterYourDateFieldHere])?

Access automatically recognizes 1,2,3 months then.
 

2 more replies
Relevance 60.27%

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

Hello:

I'm using Access 2003 on a Windows 7 machine. The database I've created tracks the purchase and sale of industrial equipment and the form that's giving me trouble is for creating/viewing quotes.

On the quote form I have a quote number field that automatically generates a new number each time a new quote is created. In the Default Value for the QuoteNumber field I have the following formula: =Nz(DMax("[QuoteNumber]","tblQuote"))+1

Generating the new number works great but the users want to be able to go back and find a record using the quote number field. In the criteria in the "qryQuote" query I have [Enter the quote number].

To control the whole mess I have a switchboard with one of the buttons being "Find quote by quote #". If I create a macro that does the search and opens the query it works. But when I add the command to the macro to open the quote form I get all records and I only want to see the one I've searched for.

This is probably way more information than necessary but - better too much than too little.

I'm fairly new to programming in Access so please be kind...

Thanks!
 

Answer:Solved: Access 2003 find using custom autonumber field

Theresa, welcome to the Forum.
The simplest way to do what you want is to add a Find Combo to the actual quote form, you don't need a button or query.
The Form design "Combo Wizard" will create the combo and associated VBa for you.
When the user types the quote number in to the combo it automatically shortens the list of quotes depending on what you type, or the user can use the drop down side bar to move up or down the list as required.
 

2 more replies
Relevance 60.27%

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

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

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

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

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

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

Is this a bug in Access?
 

Answer:Solved: Access 2010 Conditional format field on subform

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

1 more replies
Relevance 60.27%

I need to add an field to an index in a table using VB but I keep getting getting

Run TIme Error 3376 Cannot Append. An object with that name already exists in the collection.

Below is an example to try. What I ultimately want to do is add a field to an existing index but in this example I simply try to create anew index and add fields to it.

In this example, I create a table named "t New Test" then try to create an index that would be appended into the TableDef Indexes collection for that table (if I could get as far as adding Fields into the new Index in question.
Code:
Sub TryCreateTableWithIndex()

Dim CDB As DAO.Database
Dim TDFS As DAO.TableDefs
Dim TD As New DAO.TableDef
Dim F As DAO.Field
Dim NewIndex As DAO.Index

Const NewTableName As String = "t New Test"

Set CDB = CurrentDb()
Set TDFS = CDB.TableDefs

'Check to see if there's an existing table with the name we want...
'If already exists then quit
For Each TD In TDFS
If TD.Name = NewTableName Then
Beep
Call MsgBox("Cannot proceed.. a table named " & NewTableName & " already exists", vbOKOnly + vbCritical, "Table Already Exists")
Set TD = Nothing
Set TDFS = Nothing
Set CDB = Nothing
Exit Sub
End If
Next TD

TD.Name = "t New Test"
Set F = TD.CreateField("R... Read more

Answer:Solved: MS Access having trouble adding a field to an table index in VB

This appears to be a case where you have to know the "magic incantation" to make things work.

I was under the mistaken impression that you could just get a field definition from any convenient source (like the TableDef.Fields Collection) and append it to the Index.Fields Collection.

Rather, you MUST apparently use the Index.CreateField method on the Index object
and then append the resulting field to the collection.

In the example above, replace
Code:
'''''''''''''''''''''''''''''''
'This is where the code fails...
'says there's already an object with that name in the collection
NewIndex.Fields.Append F
with
Code:
'''''''''''''''''''''''''''''''
'This work nicely now
Set F = NewIndex.CreateField("Rec No", dbLong)
NewIndex.Fields.Append F
I don't know what havoc might be wreaked if the field definition appended to the Index.Fields collection is different from the field definition (of the same name) existing in the TableDef.Fields Collection.
 

1 more replies
Relevance 60.27%

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

Hello All!

I am a novice Access user. I just recently started using it to create a database for work that will track complaints. I have been searching high and low to try to figure out how to create a custom autonumber field but just don't know what to do. Maybe there is some help out there from one of you.

I wish to create an autonumber that displays the computer's date and generated numbering.
(Ex. 08/22/10-0001, 08/22/10-0002...)

Any help given would be much appreciated!

Thanks in advance,

-Julie
 

Answer:Solved: Access 2003: Help with creating custom autonumber field.

16 more replies
Relevance 60.27%

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

Hello All!

I searched around on the forum and online but didn't see any questions like mine (or that I quite understood...Remember I'm a beginner). So maybe you guys can help... My overall objective is to create a crosstab query that will show the total cost per month.

Here is what I have:

I have 4 fields (all in the same table):
Shipping Cost to Return
Shipping Cost to Send
Product Cost
Total Cost

I have a form:
That I manually enter 3 cost values in and it generates the total cost by using the formula:
=Nz([Shipping Cost to Return],0)+Nz([Shipping Cost to Send],0)+Nz([Product Cost],0)

--this formula is in the "control source" box under properties.

The Total Cost that is generated for each record doesn't show up back on my table. What's the deal and how can I get it to show up?

Thank you,
-Julie
 

Answer:Solved: Access 2003: how to create field that shows sum of other fields.

16 more replies
Relevance 60.27%

I have a table that has service information which is categorised as ServiceType, ServiceDescription, ServiceCode, etc. The are up to 200 unique ServiceDescription/ServiceCode enrties per ServiceType.

I am trying to create table for orders that will only allow ServiceType from a listbox. I then need service description to be listed as a listbox based on the selection made from service type. I then need the ServiceCode to show based on previous 2 selections.

--EG--

ServiceType ServiceDescription ServiceCode
Cleaning Clean Dwelling 001
Skips Empty Skip 002
Skips Replace Skip 003
Skips Remove Skip 004
Scaffold Erect New 005

So if I select Scaffold from drop down, it only shows Erect New in drop down for description and once that is selected, 005 is autofilled for Code
 

Answer:Solved: Access - Lookup Based On Previous Field Result?

This is fairly easy to achieve. The easiest is with the queries supplying your 2nd & 3rd Combos.
Asuming that your ServiceDescription Table contains the ServiceType ID
and the ServiceCode Table contains the ServiceDescription ID.
Create a query for your ServiceDescription including the ServiceType ID.
Create a query for your ServiceCode including the ServiceDescription ID.
In the first combo Afterupdate event procedure requery the 2nd Combo.
In the 2nd combo Afterupdate event procedure requery the 3rd Combo.
The key to make it work is to enter in to the 2nd combo's query's Criteria row for the
ServiceType ID - forms![fromname]![combo().column(1)]
where the second column of the 1st combo is the ServiceType ID.
Repeat this for the 3rd combo and the ServiceDescription ID.
 

2 more replies
Relevance 59.45%

I'm getting desperate so I'm hoping someone can help me!!

When I print a report in Access 2003 I want to hide any fields that contain a Null value. These are all text objects.

By allowing the Can Shrink property I have removed the empty values, but the field label is still being printed.

I've discovered that it is possible to do this using VB, but fair warning I am a VB virgin and know nothing So if you can help I'd be eternally grateful, just dumb it down for me
 

Answer:Solved: Access 2003, Hide field labels on reports when value is Null

The trick is to remove the label from the text box in question and use another shrinkable text box (with label removed) as the lable for the text box..

Lets say the field is [Middle Name]

if there is a middle name you want the report to show
Middle: <and then the middle name>

If [Middle Name] is blank you want nothing.. not the label, not even whitespace

So you delete the label for the middle name text box and create a new text box.

Delete the label from the new text box and put the new text box in the place where the middle name label box used to be.

Format the new text box the same as all of the label boxes so it's content will look like the labels and set it's "Can Shrink" property to "Yes" same as the middle name text box.

Now instead of binding this text box to a field in the underlying table or query, you're going to bind it to an expression that will solve to

"" if the [Middle Name] fiels is blank
or to
the text string "Middle:" if the [Middle Field] field is not blank.

Select the new text box and go the the Control Source field on the Data tab of the Properties window. In it put:

iif(isnull([Middle Name]),"","Middle:")

This will put a zero-length string into the text box when the [Middle Name] field is null and the text "Middle:" when it is not null

BUT BUT BUT--- Depending on how sloppy the data is, when the [Middle Name] field is blank, itr could be null or cou... Read more

2 more replies
Relevance 59.45%

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

Okay, I've significantly simplified my database that I was working on (see this thread for that). I thought this one would run like a charm but when I try to enter data using frmCompEntry I get the following error when tabbing out of the URL field:

Index or primary key cannot contain a Null value.

I don't know where the Null is coming from though. Could somebody who knows more about Access than me (not hard) have a look at this and tell me what's going on?

Thanks,

chris.
 

Answer:Solved: Access 2000 Null primary/indexed field error

9 more replies
Relevance 59.45%

I currently have a query that uses a multivalued field to perform calculations. The multivalued field occasionally returns one or two values that will be included in the calculations. Is there a way to exclude these values from being used in the query? I tried using the DELETE clause, but it doesn't work for queries.
 

Answer:Solved: Access: Excluding certain values (not entire results) from multivalue field i

16 more replies
Relevance 59.45%

I have a table of people data with multiple fields for the name: Prefix (Mr., Mrs., etc.), FirstName, MiddleName, LastName and Suffix (Sr., Jr., etc.). All fields are required except MiddleName and Suffix which often don't exist. I want to make this set of fields a multi-field unique index to prevent duplicate entries, and I have created the index with properties as follows: Primary=No (I already have an autonumber primary index), Unique=Yes, and Inglore Nulls=No. But the Suffix field is giving me trouble when it is blank as it often is. If a value is entered in this field (as with any other field) I am properly prevented from creating another record with the same values in the corresponding name fields, including this Suffix field. But if the Suffix field is blank/null (most are, because only a few people use a suffix on their names) I need that null value to be treated as a value for the purposes of determining uniqueness. Mr. Joe S. Blow should be the same as Mr. Joe S. Blow when both are missing a Suffix value, and I want the database to treat those names as the same, and prevent input of the second of them because one has already been entered.

As I understand it from Access Help, setting the index property Ignore Nulls=No should accomplish this. I understood this setting to mean that uniqueness is then determined on the remaining index fields, as though Null were being treated as a value for the purposes of testing uniqueness. That isn't the case. It lets me e... Read more

Answer:Solved: Null fields in multiple-field indexes in Access 2003

16 more replies