I have a date in "date to engineering" of 13/ 01/2010 but I am not getting a value in my field which should be 1479 my field is just always returning an empty field
Code:
=IIf(IsDate([date to engineering]),"",IIf(IsDate([date from engineering]),Now()-[date to engineering],[date from engineering]-[date to engineering]))
I got a table salaries master I want to extract some info out of and calculate some values. The formula below doesn't work, I'm pretty sure it has to do with [pay period] being a text field. Is there no way to make it work?
'Gross monthly Salary: IIf([Salaries Master]![Pay Period] = "weekly";[Salaries Master]![Daily Wage]*5*52/12,IIf([Salaries Master]![Pay Period] = "monthly";[Salaries Master]![Daily Wage]*20))'
I have a combo box which I define a SQL statement as its rowsource.
When I open the form, I have yet to define the SQL statement for the combo box.
My code is:
if me.cmbobox.listcount = 0 then
me.cmbobox.enabled = false
else
me.cmbobox.enabled = true
end if
But it shows the box as still enabled.
When I do debug.print me.cmbobox.listcount, it shows a value of 1. But that is impossible since I have yet to define a rowsource for it or its empty.
I also have column heading turned off too.
I have a form in which users can enter data in several textboxes to filter the listbox below it, this works great except for the fact that when a record lacks certain data it doesn't show up
Basically there are 4 filters, one for the name/id which works great, as the entries without id's show up just fine but this filter needs to be checked against the other 3 filters, for their group, education and type.
Lets use group as an example.
When a student has no group it should only show up when the group filter is an empty string. AFAIK the wildcards should see to that.
Currently however, when a student has no group, it doesnt show up at all unless I remove the | Like '*' & [groepFilter] & '*' | part from the query.
I've never used complicated WHERE's like this so it might be something very simple. I think I could get it to work using VBA and modifying the rowsource of the listbox from there but this would be a lot easier and I'm curious as to why this doesn't work.
The SQL:
SELECT Student.Studentindex, Student.studentid, Student.studentvn, Student.studenttv,
Student.studentan, Student.Groepcode, Opleiding.opleidingsnaam, Opleiding.type
FROM Student LEFT JOIN Opleiding ON Student.opleidingid = Opleiding.opleidingid
WHERE (((Student.studentid) Like '*' & [naamFilter] & '*') AND ((Student.Groepcode) Like '*' & [groepFilter] & '*')
[Code] ....
date formula that I was using in access 2007 doesn't seem to be working in 2010.
The formula that I had was
Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1)
And DateSerial(Year(Date())+IIf(Month(Date())>3,1,0),3 ,31).
The calculation allowed me to count holiday hours taken between 1st April and 31st March. The problem is that it was working up till 31st march but is not now showing holidays taken since 1st April.
I have a continuous form that is belong to values of three unbound fields.
[TestName]
[FromDate]
[ToDate]
and a commandbutton that makes requery for this continuous form.
when i click on this command button , if any field is empty ; what's the appropriate code that i can use to alerts me the name of the forgotten field ?
I would like to count a field if another is empty. I have 2 fields "registered" and "Started" and "closed"I would like to count all the fields "registered" if the field "started" is empty. I also want to do it with "started" if "closed" is empty.
View 2 Replies View RelatedI've got a memo field on a form where the name is TextEXTRA
The Control Source for TextEXTRA is EXTRA.
I've got a box called BoxSHOW (Visible = No)
As I browse through records or find records, I want the box to become visible when there is something in the EXTRA field and become invisible when the EXTRA field is empty. This is what I've tried .....
Private Sub Form_Current()
If EXTRA Is Not Null Then
BoxSHOW.Visible = True
End If
End Sub
I tried many variations on the first line such as ...
Me.EXTRA "EXTRA" TextEXTRA
but I always get errors.
Hello all,
I have combo boxes populated with all values in a particular field, then shunt the form onto that record (standard combo box for selecting records from a list in other words).
Problem is, these boxes seem to stop working if I leave the form for another and then return to it.
Here's the code Access puts behind:
-----------
Set SerialRS = Me.Recordset.Clone
SerialRS.FindFirst "[Serial Number] = '" & Me![Combo60] & "'"
If Not SerialRS.EOF Then Me.Bookmark = SerialRS.Bookmark
-----------
The form may be opened and closed via other processes (although never unloaded), but when I come back to it these combo boxes always stop working! Why?
I am using Access 2007. Second, I am using two tables, Inventory and Service Request. Inventory is a list all the Inventory, with has things like location (building & room number), type (desktop, laptop, etc.) and access tag number. Service Request also has Access Tag Number (should link back to Inventory) & date of reported/resolved problem and description of problem/solution.
On my form for Service Request, I have the access tag number as a fill text box, when you double click on that text box, it runs a Query that asks for the 1) Building, 2) Room Number & 3) Type . . . Query currently opens in a the spread sheet view that shows those three things but also the Access Tag Number associated to them.
I would like that Access Tag Number to just return into the field that was double click to start the query.
Is this possible? If so what am I missing?
So I have built a form and added a field using the expression builder which is returning #Name? There is a relationship between the table on which the form is based and the record I am adding to a control.
View 1 Replies View RelatedI have a form in my db and i would like it to show me a warning message if a specific field in a record is empty or even better not to allow me to close the form. I would like to have this in order to avoid incomplete data.
View 2 Replies View RelatedI'm using a form for book drinks to a customer room.
To find the customer, I'm using an search function (Unbound field), works fine.
The same function I'm using in the subform, for booking the articles to the customer.
Now, I'm wondering, is there an easy way to clear the search field either when starting the form or after the search is done and the article booked.
The search function is based on the macro (Search for record, condition: ="[ID] = " & Str(Nz(Screen.ActiveControl,0))).
I'm working on a table for work which will serve as a database of bins and the products currently in those bins, as follows:
ID Bin Amt Product
1 34 25 110001
2 33 15 200005
3 32 23 110003
etc.
Basically: employee A will use a form to update "Bin 34" (ID=1), with information (Amt, Product) until it is emptied by employee B and cleared using a separate form. No new records will be made or deleted, just the adjacent fields (Amt, Product) cleared and filled in and cleared again over and over.
What I'm trying to figure out is, when employee A clicks on the combo box on his/her form to select a Bin and enter information, how can I filter what is listed in the combo box to display only those bins which have no values under Amt & Product. The real-world risk of overwriting a bins fields before it has been cleared by employee B (who physically empties the bin) can be extremely high monetarily so I am trying to reduce that risk by eliminating it from the combo box until employee B has cleared those fields.
I am trying to created an IIF statement in a field I have in a form.
I have entered the following statement but it keeps coming up with the following error and I cannot see where I am going wrong.
The expression you entered contains invalid syntax, or you need to enclose your text data in quotes
=IIf([RenewalInstallment]=0,(12-[MFAccepted])*[CurrentAnnualPremium]/12)*(1-[DiscountAccepted]/100),IIf RenewalInstallment]<>0,(12-[MFAccepted])*[RenewalAnnualPremium]/12)*(1-[DiscountAccepted]/100)
If I enter the first part of the If statement i.e.
=IIf([RenewalInstallment]=0,(12-[MFAccepted])*[CurrentAnnualPremium]/12)*(1-[DiscountAccepted]/100)
The field calculates correctly however there seems to be something wrong with the nested if.
I've got a table set up with sales info and all other kinds of data. How do I get a formula to work? Here are the inputs I have: Total sales (by outside company), total purchases (from my company). I want to know what percent of their total sales are made up of the poducts they purchase from me and have that field update automatically. Its a simple formula really, I just don't know how to enter it properly. Thanks.
View 2 Replies View RelatedHey guys hope someone can help me out here...
i need to have a currency field in one of my tables, the user will enter the price in euros, and the output will be in pounds... therefor i need the following formula to work:
[enterdprice]*[current exchangerate] = price in pounds
i would very much appreciate suggestions/help
hi i have a text field in with this calculation
=([Total amount requested]+[text3])/[Initial valuation]
it displays a div/0 error which i want to remove, i know i need to put this calcualtion in an iif statement but whenever i try and make the formula it never works
=iif([Total amount requested]+[text3])/[Initial valuation],?,""
where the question mark is i am not sure what should go there, as i want the total to of the calculation there and if 0 hide 0 with the ""
this has been bugging me for a while now and i can just get my head around it, probably very simple for someone to answer
regards
chris
Hi all,
A nice easy one for you. I am creating a database, one of the fields is total cost. Is there an easy way to have another field, which calculates the VAT without using a query? I would like this done as soon as the total is entered.
I have done this using a query, but didnt like this way.
I am sure there must be a very simple way of doing this but it has escaped me.
Help
Phil
I am trying to make a query using fields from multiple tables. I used the Expression builder to create it and it is a formula field where multiple table fields are involved. But whenever i run the query Access asks for a parameter value in a modal dialogue for the value of the fields. Is it because the field names has Square brackets around it? But access puts that automatically. How to make it work properly?
View 11 Replies View RelatedI have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail.
I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
How can this problem be rectified?
How to Make an empty field schedule database ACCESS default text in case the field is empty ....
Default text like 12345
Hello World:I have two tables, one called Assets and the other table called Job Sites. the Assets table has several fieldsAssetID - autonumberplus othersthe Job Sites table has two fieldsJobSiteID - autonumberJobSite - text (with about 22 names in it)the I add the JobSite to the Assets table (hiding the JobSiteID) it shows me the expected drop down box but it is BLANK. If I click on any of the BLANK spaces in the drop down, it populates the field. It is behaving as if the font color was white.Any ideas??? I am baffled. :confused: :confused:
View 8 Replies View RelatedI am trying to make a query with two tables. Each row consist of a student ID, their name and all their personal information. Both tables are exactly the same. But one table is a link table. That link table gets update every so often from an outside program and the other table is not linked. I want to be able to run a query that updates certain fields called "address changes". I have figured out how to find the updates, but I am having a problem with a field that is blank. If I have a field in the non-linked table that is empty but there is information in the linked table, it does not see it. Is there a way to get around this empty field problem?
View 3 Replies View RelatedI need to add a text box to my report that shows the value of a record in a certain field that is corresponding to a given value in another field.
For example, if my report shows the following:
N City
3 D
7 F
2 H
9 K
4 A
the text box should show the name of the city corresponding to the largest number which is in this case "K". is it possible through formula builder?
I'm sure this is a simple issue, but so simple I can't find the answer.
I have a recipe DB and have calories and fat and fiber fields, I want to have another field which will display a point count for a formula (calories/50+Fat/2+fiber/5) but the catch is if the fiber number entered is over 4 I only want the calculation to use 4 max.
Not sure how or where to create this formula to populate a field in the DB.
As you can tell I'm new to all this and any help would be appreciated.
Thanks in advance
Mike