Here is one that I could use some help with.
I have an Updates field in all of my tables. It, of course, holds my audit trail. Im tryiing to figure out a way that I could run a single command and clear the contents of all the Updates fields on all the tables in my db. Ideally, I'd like to auto generate a report for all the update fields on all the tables, then do a purge.
Any ideas?
Hi,
I'm making a form where the contents of a field is determined on the contents of another field in another form. I thought an IIF function would work, but when I tried it the contents says #NAME.
I put it in the control source.
IIF([Forms]![Frm_NewBusiness]![Page4]![Child51]="NTU",NewBusiness_Date_Issued="NTU",NewBusiness_Date_Issued)
Any ideas?
Cheers,
Ben
I have 2 forms:
one called basic the other called advance
how can i automatically delete records from the basic table, when students are advance.
thanks 4 helping
I have a table in my Database that uses the feilds Location and partnum. I use a barcode scanner to generate each of these fields. The problem I am having is that when our part number is scanned it contains data such as "des--. 123456 --aa". I have been doing a find/replace on this data to remove the "des--." and "--aa" but I was wondering if access has a function to do this for me so that I can get a macro to check automatically for this type of problem. Any ideas?
View 1 Replies View RelatedHow do you have Access delete records automatically from a temp table?
View 1 Replies View RelatedIm trying to make a basic customer database and pos system. The part iam having trouble with is, I have two tables as follows:
Inventory: orders(invoices):
productId ProductId
amount (in stock) amount(sold)
pricePerUnit CashTendered
I want the amount(in stock) sold in each order to be deducted from the total in the amount(sold) automatically.
I'm an amateur (is there such a thing as having databases as a hobby?) with little training. I volunteered to organize a growing volunteer group I'm a part of by making a simple database for the volunteer office staff. One of the things I want it to do is make name tags for our weekly meetings. I don't want to print one for each name on our list because the list is over 400 names long with only around 35 regular attendees. I made two tables--one is the MakeNameTag table that lists regular attendees (it is a lookup field on the Names table).
The other is the Absences table. It has two fields; DateOfAbsence and MakeNameTag. If someone misses a meeting (does not pick up their name tag), their name is added to the Absences table. A query then filters the table for dates in the last 28 days. If their names shows up four times in the last 28 days I want for their name to be deleted from the MakeNameTag table. How to do that automatically? Our office volunteers have minimal PC skills, so the solution needs to be very user-friendly.
my database just records end of day sales figures which are entered though a form, after they are entered the total for the day is archived. However the problem i have is that the i need some way of clearing the input table with a command in the form.
View 2 Replies View RelatedI am trying create some code that checks if the contents of the status field in a table is "Authorised" where the ID = something specific for multiple records. If all records witht the specific ID are "Authorised" Then generate a new record in another table. Where to start, perhaps a Dlookup?
View 1 Replies View RelatedI have a report that has multiple fields - 3 of which matter for this discussion -
1.) employee
2.) employer
3.) rateofpay
The employer field contains 1 of 2 options. For the sake of this conversation, lets call it ABC123 and XYZ123
I need to be able to calculate the average rate of pay for ABC123 company, and exclude the rate of pay for XYZ123 company in my report.
I am calculating this in a section footer.
I am trying to perform a search function query in access but am having trouble. I have two tables. One table has a column in which I need to search the contents of this field by referencing data in another field/table. I am able to write the query searching the data manually (not referencing the other table) but can't seem to get the query right when referencing the other table!
this query works =
SELECT *
FROM Sample_Data
WHERE (((Sample_Data.[Affected_Frequencies]) Like "*451.425*" Or (Sample_Data.[Affected_Frequencies]) Like "*451.400*"));
but, i put together a frequency table (Table name = Frequencies) and the column within that I am searching for is called Frequency. im trying to reference Frequencies.Frequency using a LIKE statement.
Is there a way to make the value of one field change dynamically based on the values in other fields on the form?
Here's the general idea. I have two tables.
RecordTable
--------------
SiteID, etc.
SiteTable
-------------
SiteID, SiteBlurb
I want have a form, where you can change the SiteID value in RecordTable, and have the SiteBlurb change (i.e. the form pulls the information from SiteTable) dynamically once you have entered the new value.
is there an alternative to making a field blank, other than my old trusty <Me.Field1 =""> ... ?
(my use of the < and > are purely for demonstration purposes, and do NOT get used in code)
I'd like some fields to be native, virgin, blank, et cetera... I do find sometimes I have to overly complicate a VBA statement by putting.. <If IsNull(Me.Field1) or Me.Field1 = "" then> because I have made that field value = "" to blank it out.
My company requires a query to aid distribution, however I am experiencing some problems implementing the system.
We have purchased a large database of addresses and need to be able to query a town/street to find out how many houses are in that street.
I have the query and form set up to display the street/town and list of house numbers when searched for, however the houses are grouped by postcode rather than street, with multiple house numbers in the same field (see example).
Street
Main Avenue
POSTCODE
HG23 0DF
Houses
89;91;93
POSTCODE
HG23 1DF
Houses 95;97;98
----
Basically, I need a total of how many individual houses there are in a street, regardless of postcode. In this case, it should treat the ";"s as dividers and return the result of '6'. Or in some cases ignore the semicolons as some fields look like ";;;;;;;;13". It should then add both the total for one postcode to the other postcode to display a complete total of houses in one road.
Can anyone make any suggestions?
Thanks in advance,
Matt
HI
Is there a way that i can split the fields based on the delimiter that i will set in a query?...
for example : 088H-FJAKSDF-SAFN-F
in that case additional four columns will be added becuase it will be separated by a dash (-)
thanks so much for your help.
I'm an excel user and in using excel I can take two fields and combine their data in one field. I've got about 6 database files with anywhere form 1000 to 3000 records that have already been prepared and imported into access database files .mdb and I realize that the firstname lastname fields need to also be combined into a contact field with both names. In Excel it's easy in access I'm a little lost. I was looking to an update query however I can't find instructions on what I'm trying to do.
View 2 Replies View RelatedI want to print text based upon the content of the field but need more than the IIF statement. For example, if the field contains an "A" I want to print "Active", "C" I want to print "Closed", "N" I want to print "New", etc.
Thanks in advance for any help. You guys have always had an answer in the past!
I am trying to view the contents of a text field in a form. The field can only be a limited size in the form, but I want to store a lot of text in the field and have it visible without having to enter the field and use the arrow keys to view the contents. Is it possible to programme a function that shows the contents of a field when I roll the mouse over it or something like that? The "Control Tip Text" is the principal of what I am trying to do except this gives a fixed value. When used in web page development I think this function is refered to as "Alt tag"
View 3 Replies View RelatedHi,
I have some queries that I created in 97 and convert to Access 2000.
However, when I go to query DESIGN view, I would like to see all the contents of long functions (iif) that I wrote for some fields as a result of running the query, but each field only shows a portion of it (as if it was truncated). I also use the "Zoom..." of that field or Shift+F2, still the same thing, only shows portion of it, the last portion was truncated.
Do any of you know HOW to show all, so I can check and revise if I need to, pllllllease.
Thank so much,
I am in the process of revamping an old database. I have a main table that currently has two fields "Name" and "Job" but will have more. I have another table with the same fields. I want to use as a lookup to populate the main table with a user form from the second table. I can populate the "Name" field using a combo box, but my problem is how to autopopulate the "Job" field. E.g. If the user selects Mr Smith from the "Name" drop down list, the corresponding "Job" populates the second field.
View 4 Replies View RelatedI need to use an iif statement to search a field (column) for a certain word. The field is in tblEquipmentName and the input is in tblWorkReport
Ex.
iif(input from tblWorkReport is in field from tblEquipmentName,...,...)
Running 2007
I never ran into this problem before.
I have a qry, with showing totals (group by). One of the fields is called comments, which is a memo field. I am only seeing partial comments.
When I redesign the query and take out the "totals", I see the entire comments.
Is there a fix to this?
Hello,
I have two tables which contain the following fields (simplified);
Table 1:
ID
Startdate
Enddate
Table 2:
ID
Unitdate
Treatment
The tables are related (one-to-many) through the ID field.
In operation, the user first enters the Startdate and an Enddate for a patients' hospital stay in Table 1. Following this, the user enters the Startdate again in the Unitdate field in Table 2 and selects a Treatment from a drop-down list. After this, the user enters the following in the Unitdate field and selects a treatment. This is repeated in consecutive order until the Enddate is reached.
The end result looks like this;
Table 1:
ID Startdate Enddate
123456 01/01/2005 04/01/2005
Table 2:
ID Unitdate Treatment
123456 01/01/2005 Ventilation
123456 02/01/2005 Ventilation
123456 03/01/2005 Haemofiltration
123456 04/01/2005 Ventilation
Does anyone know if there is any way to restrict the Unitdate field in Table 2 to only allow dates to be entered that are between the Startdate and Enddate fields in Table 1? Ideally, I would like the Unitdate to populate automatically with the dates, starting with the Startdate value and ending with the Enddate value and filling in all the intermediate dates. Also I would like to make sure that there is no way of having duplicate dates in the Unitdate field.
If anyone can help, I would be extremely grateful.
Best wishes
Russell
I've got some code that loops through a record and copies the non-null fields to another record. This is the partial code:
For Each fld In tdf.Fields
If Not IsNull(Forms!contact_lookup![Contact_sub subform1].Form(fld.NAME)) Then
strSql = "UPDATE tbl1 SET tbl1.[" & fld.NAME & "] = '" & Forms!contact_lookup![Contact_sub subform1].Form(fld.NAME) & "' " & _
"WHERE tbl1.[FC_APN] = '" & Me.txtApn & "';"
DoCmd.RunSQL strSql
End If
Next fld
This works fine until it encounters a field whose contents has a reserved character, like a single quote (" ' ") in it, for example, "What's the what." At that point the SQL thinks there's a " ' " missing in the statement, and I get an error 3075 (syntax error: missing operator).
Imagine that you see a column of values and then a total at the bottom. The only issue is that the total value does not appear until you click on it.
Any setting that would cause a total value in a footer section to not appear automatically?
Behavior is noticed on reports as well as subreports. An image is attached.. Imagine the $37,000,000 simply not appearing until it is clicked with the mouse.
I have a field on a form that is the results of a dlookup to a table.field. I want the value to be updated when I execute a button that is running a macro. How do I do this automatically, and not have to hit the refresh button?
View 5 Replies View Related