Help With A Simple DLookUP() Formula

Oct 3, 2005

Hi there I am embrassed asking about this but I really need some help.

I have been trying to teach myself Dlookup with the help of the forum and help but still can't get it to work how I want it to.

What I am trying to do is for the function to look at a table for a value based on a textbox within a form. If the value exists I want to display a msgbox saying the value exists.

This is what I have been trying,

Private Sub Command7_Click()
Dim varX As Variant
If varX = DLookup("txtFileName", "tblImportInformation", "[FileLocation] = txtFileName") = Null Then
MsgBox ("Value is not in table")
Exit Sub
Else
MsgBox ("Value is in table")
End If
End Sub


Please help!! this is driving me nuts!

View Replies


ADVERTISEMENT

Simple Formula Problem

Jan 2, 2007

Note: All names and formulas in this post appear without the quote marks in the system

Hi.

I am sort of a n00b I uppose when it comes to MS Access. I never really used it before I started doing my A2 ICT coursework. My analysis needs to be in this Thursday and I also need to finish my tables and relationships; however I cannot continue if this formula keeps not working:

I have a field called "CostofQuote" and I am trying to get the value of that cell, divided by 2, to appear in another cell called "DepositPrice".

In the 'Default Value' text box of "DepositPrice" cell, I have tried many formulas and used the expression builder. Some formulas I have tried are "[CostofQuote]/2" "[CostofQuote] / 2", etc.

I keep getting the following attacted error messages, basically saying there is no field called "CostofQuote".

Please can somebody help me A.S.A.P? I would really appreciate it.

Thanks in advance,

‹-cJr-›

View 5 Replies View Related

Trouble With A Simple Dlookup

Apr 12, 2006

I am trying to use dlookup to fill an unbound textbox on my form. I know I am doing a simple thing wrong and I cant figure out what it is.

My form has a field from table(m5Warehouse) called M5Location. It is a bin location. My form has an unbound textbox called BinMax.

I have a table called tblbinmax that has 2 columns. Binnumber(same as m5location) and binmax(maxinum number of pallets that can be placed in bin)

When i fill in M5location on my form, I wanted code to fire on afterupdate to look in tblbinmax, look at the binnumber and put in the value in unbound textbox called BinMax.

This is my code: me.binmax.Value = DLookup("[binmax]", "tblbinmax", "[binnumber] =" & Me.M5Location)

I have tried every way i can figure to make this work, and i keep getting error message, name?#, etc. I get error msg 3075 and error msg 2001...PLEASE HELP!!

View 2 Replies View Related

Simple Dlookup Probem

Aug 15, 2006

I have a form called 'Order Details' that contain a field called 'Name' and combobox called 'Product Code'. The combo box is sourced from a table called 'Titles'.

When the value in the combobox 'Product Code' is changed, I would like the 'Name' field to lookup the corresponding value from the 'Titles' table. But I can't get Dlookup to work. And it's driving me mad! Here's the code I'm using as the Control Source for the 'Name' text box:

=DLookUp("[Title Name]","Titles","[Code]=forms!Order_Details![Product Code]")

View 2 Replies View Related

Convert Formula Into Formula Result

Apr 6, 2007

Hi All:running Access 2000.I have a qry with a formula field that performs some calculation (Extension: CCur(([OnHand]*[Price])/[UnitsPer]))and it works fine. How can I update the actual table with the result of the calculation for every record? The problem I have is that there's some null values in the results therefore when I try to get totals on that field, it errors out saying "invalid null use"thanks

View 7 Replies View Related

Using Query Formula Information To Create Another Formula In A Seperate Query

Nov 20, 2006

I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!

View 2 Replies View Related

Simple Question For A Simple DB

Dec 29, 2006

Ok I am right now making a simple Vendor/Product database to create a line sheet for some sales folks. I have 3 tables: Vendors, Products, and an associate entity Vendors_Products to relate the two. I have a form currently that draws the Vendor Name (primary key) from the Vendor table and the Product Name from the associate entity. This allows me to create new vendors and select current product types from a drop down box. The problem is that the drop down box is too long and it is tiresome when 1 vendor has 10 product types.

Can anyone tell me how to resolve this? I thought it would be better to have option buttons and display all available products. Then you could just click all of the option buttons that apply to that Vendor and it would create the relationships...is this possible?

View 1 Replies View Related

Formula Help

Apr 28, 2005

i have a list of check boxes which state whether a ticket has been sold or not. what formula can i use to find the percentage of tickets that have been sold (by counting the number of checked boxes). also how can i implement that onto a report to display it next to a pie chart of the information. if it cannot be put on a report how can i put it onto a form?

thanks in advance

View 6 Replies View Related

Formula Help

May 23, 2005

I have a nice database which is working well apart from one part

I have 2 date fields, you enter in any date in the first and i need a formaula which will generate a date 6 weeks in the future

can anybody help or suggest what to do?

View 1 Replies View Related

Formula

Apr 16, 2007

Hi

I am trying to create a formula that will update a field in my table - I have created an update query and want to update the field GPA.

I have tried using the iif formula without success. Here is what I want to calculate.

I have two fields one is Score the other is Baseline. The formula needs to place the Score of 4.0, 3.5, 3.0, 2.5, 2.0, or 1.5 in the GPA field given the following.

If the difference between the field score and baseline is .00 then the score 4.0 goes in the GPA field, if the difference between the field score and baseline is .05 then the score is 3.5 in the GPA field, if the difference between the fields score and baseline is 1.0 then the GPA is 3.0....

If both fields are equal then score is 4.0
.05 score is 3.5
1.0 score is 3.0
1.5 score is 2.5
2.0 score is 2.0
2.5 score is 1.5

Can anyone help me with this formula.

View 4 Replies View Related

Sum Formula?

Apr 6, 2006

I have a customers table, products table, and a transaction table that brings them together fields in tblTransaction:

Transaction ID
Order Number
Customer ID
Title ID
Date
Status

I want to create a query so that you can type a name of a customer in, and it will bring up all the items they have bought, and will add up all the prices (from tblTitle) and show it at the bottom but im not sure how to get this calculated field into the query? Whats the sum formula that you put into a query?

View 12 Replies View Related

Formula...

Nov 7, 2006

I have a form that people fill in where they can choose from 4 boxes (and they can tick as many as they want - it is a yes/no box)

I have tried to write a formula for a queryto show where none of the boxes have been ticked.....this doesn't work:

=(IIf(IsNull([Q3(i)]) And iif(IsNull([Q3(ii)]) And iif (IsNull([Q3(iii)]) And iif(IsNull([Q3(iv)]),"1","0"))

???? what should I write?

View 3 Replies View Related

Formula

Nov 4, 2004

Because I am stupid at math, can some please help me out with the formula to get the percent of something that has changed.

Say I have October usage of 2685 and November usage of 6294. I need to know what the percentage of the change is from Oct to Nov Basically the formula to make this calculation.. The calculation will be done in Excel. I know this is an access help area, but you guys have really helped me in the past.

View 2 Replies View Related

Formula

Mar 21, 2008

I need a formula that will give me the count of the number of files I scored 3.0, 2.5,2.0,1.5, and 1.0. These scores are in one field called Claim Quality Index.

What I am trying to do in a query is to count the number of files I scored a 3.0 and in the next column of the query give me the count of the number of files I scored a 2.5 - same for score 2.0, 1.5, and 1.0.

I have chosen the count in the query but I cannot get the criteria to only give me the above. I believe I need to use the expression and name the field CQI3: ????(formula that I cannot get right.

Can anyone help - I have tried various ver. of the IIf formula but to no avail.

View 10 Replies View Related

Sum Formula

Jul 6, 2006

I have the following idea in my mind. The only problem is that I have no clue how to make this work.

[IMAGE]

Anynone know how to make this happen?

View 14 Replies View Related

Need Help With Formula...

Dec 7, 2006

Could anyone give me information on learning how to use the formulas in Access 2003? A good book, site..etc. Example I need to know the formula that takes
20061015 and puts it into 10/15/2006 format. Any help is appeciated!

View 2 Replies View Related

Formula?

Feb 1, 2007

I am trying to Automate the creation of a quote number. I plan on making a form that contains a SalesID ComboBox and a Command button that opens a new form. On Click I want the new form to open a new form and insert the created quote#

Basically the number needs to consist of:

The letter "Q-", "Date", "SalesID" (selected from the drop down menu) and "-#" (sequenced number)

this should give me something like this:

Q-13107BOB-1
Q-13107BOB-2
Etc.

How can I do this?

Could someone provide a name for what i am trying to do so I can look it up on Google?

View 1 Replies View Related

Abs Formula Help

Jun 9, 2007

Hello, I have a formula on my report in a text box that just shows the total delay for a crew by using the supervisors name. This information isnt stored anywhere its just to show a percent on the report! the formula is:

=Abs(Sum([Supervisor]=”Chad Zablackas”)*([DT REGULAR]+[DT MAINTENANCE]+[DT Reason 1]+[DT Reason 2])/Abs(Sum([Supervisor]="Chad Zablackas")*[EMPLOYEE TIME]))

I keep getting an error saying invalid syntax: Your may have entered an operand without an operator? What am I missing in my formula? Thanks!

View 6 Replies View Related

Formula In Access

Jan 10, 2006

Ok so I'm kinda new to access. What i want to happen is for there to be a textbox on a form that changes depending on what is in the other text boxes. Eg 1st box = 1, 2nd box = 2. I want the third box to equal the ammount of the first two added together (textbox1 + textbox2 = textbox3) how do i do it? Where do i put the formula. Thanks.

View 12 Replies View Related

Formula 1 Database

Oct 23, 2006

I am a big Formula 1 fan and I am thinking about creating a F1 database. However, I can't decide which is the best way to store the data. I don't know if one race should be a one field or if one driver for one season should be one field. Obviously I want to store it in the best way possible so that I can easily pull out relevant data in queries. My knowledge of Access is relatively limited so any help you could give me would be appreciated.

View 2 Replies View Related

Formula In A Field

Apr 27, 2005

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 Related

Formula In Field

May 31, 2007

Hey 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

View 3 Replies View Related

Parameter On An Formula

Mar 18, 2006

Hi, i have created a query where for 2 fields i have used a formula, and for the 1 field i need to do a parameter on it, but im having a bit of trouble with it, instead of 1 dialogue box appearing asking for the parameter value, i get the 1 i want and 1 for the other formula i have created. any ideas how to solve this ??
thnx chia

View 1 Replies View Related

What's Wrong With This Formula???

Dec 8, 2006

Here it is:

=Count(IIf([Calendar Date]>=[First Day] and <=[Actual Last Day])

???
Maria

View 4 Replies View Related

Error In Formula

Jan 19, 2007

Hi,

I've currently got several formulas like the one below in my query for which when dividing by 0 I get #error (which I was aware occurred). However, I though my formula would return 0 in such a case (lilke Excel would), but it hasn't

The fields which it is using to calulate the formula have number values, for which if no score is entered then the default value is 0. I would rather not leave this blank and retain the zero value (I haven't looked in to nz function any further because this if for blank fields?) so any help is gratefully appreciated.

The forumla returning #error is:

UKAdj%: IIf(IsError([UKAdjTot]/[UKTotal]),0,([UKAdjTot]/[UKTotal]))


Cheers,
Iain

View 6 Replies View Related

Query Formula

Mar 6, 2007

I am new to the access database world- I have a database in which I have projects that each have a priority- to get the priority # there are about 10 categories that need to total up to give me that priority #

Every formula I have tried, has given me an error message of some sort! PLEASE HELP, and please let me know if I am not explaining this enough!:confused:

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved