In a form named PRODUCTION there are 3 text box for invoice data: xtype, xserie, xnumber
I need valid this data from the INVOICE table, where its key is a compound key (TPINV+SERINV+NUMINV)
I tried with:
Private Sub xnumber_AfterUpdate() Dim searchinv as string searchinv=Dlookup("*","[Invoice]", [tpinv]="& Forms![production].[xtype] and [xserie]= &Forms![production].[xnumber] and "&Forms![xnumber] If searchinv ="" then MsgBox ("Invoice doesn't exist'") Cancel = True End If End Sub
Hi guys! Would appreciate any suggestions that people have for my problem. How do you perform calculations in queries between tables? Do you use the DLookup function to update the values in the query, if not how so? I've heard from somewhere it may be useful to use the DLookup function, but i'm not really sure what this is!
I am trying to default a value in a field using the DLOOKUP function. I have attached the database. The Form is FrmDailySalesInput and the field is Trailer. I have set the default value as follows =DLookUp("[Trailer]","TblStoreInfo","[Store]=[TblStoreInfo]! [Store Location]"). I have seen alot of different syntax on this forum for DLOOKUP. I am using Access 2000 presently.
Also I want to default the value in but be able to change it if necessary.
I am receiving #Error in my unbound textbox where I am trying to use the DLookup Function to lookup the value that corresponds with "ADPCompany" and "Location Number" fields. Here is what my DLookup Function looks like: ' =DLookup("BranchNo","tblAllADPCoCodes","ADPCompany=" & Forms![frmMarthaPerPayPeriodDeduction]![ADPCompany])&","& DLookup("BranchNo","tblAllADPCoCodes","LocationNumber=" & Forms![frmMarthaPerPayPeriodDeduction]![LocationNo]) '
I'm not sure if I have the right syntax to include to things in the criteria.
I was wondering if someone could point out what I'm doing wrong...
I have a (parent) form with 2 subforms. I would like the field in one subform to look at the value in the other subform... if that value is null I would like the field to display "N/A" otherwise I want it to perform a DLOOKUP....
I have the following but I'm not sure if I'm referencing the subform field correctly... I keep getting the #Name? when viewing the field from the parent form's display.
I am receiving #Error in my unbound textbox where I am trying to use the DLookup Function to lookup the value that corresponds with "ADPCompany" and "Location Number" fields. Here is what my DLookup Function looks like:
i am trying to executed q query which has a Nz function. this works fine when exceuted from access. but when i try to executed the same from Vb i get an exception
"Undefined function 'Nz' in expression"
can anyone point out why this is happening? and wts the solution?
I am having a problem with computation of monthly actual generated from the Amount column which is in a purchase order table..... The monthly budget amount is generated in a query named category query... I have computed like Jan Feb Mar till Dec the column fields in the query.... A form named PO form has been created with the category item combo box ....
The problem is when a user selects a combox box he or she is presented with the monthly actual textbox that shows the amount computed( on a fiscal basis(not calendar days)) and that is done by looking up at the date converting into a month and then computing the amount for that particular category item selected...sum done if there was the same category item like repairs and maintenance done for this amount on the same month different date...I need a way to look up at the date convert into a month and then show the sum in the field textbox using the Dlookup function ..>Can this be done??)
Monthly budget I cant figure a way to get the monthly budget by looking up at the date and show the computed amount on a "fiscal basis" since the monthly budget for different months jan feb mar is computed seperately in seperate fields in the query
I have a form where I am using two combo boxes the second combo box fields are being based on what is selected from the first comb box. When I enter the DLookup criteria into the text box I am trying to lookup based on the selection of the combo boxes it returns nothing. Here is what my DLookup looks like: DLookup("[BranchNumber]", "[tblAllADPCoCodes]", "[ADPCompany]= '" & [cboADPCompany] & "' And [LocationNumber]= '" & [cboLocationNo] & "'")
I can't seem to get past this expression/criteria problem.
[Actual] is my field I want to grab data from [L3-4-5] is my main table [CDATE] is a form control where the user enters a date [Quarter] is a number field and want it to equal 1 [Partname] is a text field and string it to equal 1
My current expression is:
=DLookUp("ACTUAL","L3-4-5","ID=" & [CDATE] And "[QUARTER]=1" And "[PARTNAME]='1'")
Although my other expression on another form works.In my if code statement I have
I made a custom function to look certain value from table based on couple of criteria that it gets from query where I want to use it. Function's code is below:
Code: Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant Dim priSubID As Long Dim priLocusID As Long Dim priResult As Variant priSubID = nrPotSubID
[Code] ...
However, when I use it in query it only returns Case else - option and everything else is empty.
I have a form based on query. On form i am retrieving data from another table using DLookup in a unbound text box. So I want to save the result of DLookup function in another field/table on same form.
The help for junctions says to make a compound key in the junction table, but I don't understand the intructions for this. Do they mean make all three of ID's the junction table PK's or is there a literal "Make a Compound Key" button somewhere?
Currently, the table has an AutoIncrementing field as the PK and the two FK's copied in from the Primaries (converted from Auto to Number). As a result, I can only make one-to-one relationships to the junction table.
I am trying to enforce referential integrity on tables where a compound key is implemented.
I want to enforce integrity on the DETAIL table so that it can only use an SOR_ID from the SOR table that has a corresponding PhaseID in the HEADER table. Here is my current diagram:
The only solution i can think of at the moment is to build two queries. One which concatenates SOR.PhaseID & SOR.SOR_ID, and another which concatenates HEADER.PhaseID & DETAIL.SOR_ID... and then create a relationship between the two queries.
There must be a much nicer way of doing this though?
When you create a column chart and then change one of the series to a line, how can you then apply formatting to that line? I've attached a stripped down database showing what I'm trying to do. Series 1 shows individual monthly values (percentages) and series 2 shows the target which is 80% for each month.
The problem is that although I can apply formatting to the column series, I can't see any way of selecting the line series so I can set the formatting I want. I'm using Access 2003, on Win XP Professional, in case that's important.
I'm getting back into Access after retiring and about 10 years of isolation (from Access). I have 2010 version and know that one should avoid duplicate entries and a way to do this is make those fields unique key fields. I have a Customers table and have bounced back between CusID (AutoNumber) and Compound Keys (CusFName and CusLName) as the key fields. The compound keys prevent duplicates but become very hard to work with later in code and expressions. The CusID is preferable from that standpoint, but can't prevent inadvertent entry of duplicate names.
I have 3 table table; Invoice table, Product table and Sale_product table. Sale product table records all sale from the product table
Invoice table has these fields ID TOTAL CASH_TENDERED CHANGE
Product table has
ID CODE QUANTITY NAME PRICE
and Sale_Product table has these ID PRODUCT_CODE QUANTITY PRODUCT_NAME PRICE SUBTOTAL INVOICE
I did main form from Invoice table and sub form from Sale_product table. I want to use DLOOKUP function to load the name and price, quantity and calculate subtotal automatically from the product table based on the product code entered. i have being trying hard and i keep on getting "Name? error"
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total ----------------------- 2 | 1234 | 80.00 ------------------------ 1 | 43526 | 20.00 ------------------------ > | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer) Dim SPSelect As String SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE" SPSelect = SPSelect & " ItemID = '" & ItemID SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields. 160037 213137 224356 235716 235800 12341 21708 22732 Here is the date function we use: Function f2Date(strDateOld As String) Dim strDate As String, strMonth As String, strYear As String strMonth = Mid(strDateOld, 5, 2) strDate = Right(strDateOld, 2) strYear = Left(strDateOld, 4) f2Date = strMonth & "-" & strDate & "-" & strYear f2Date = CDate(f2Date) f2Date = Format(f2Date, "mmmm d yyyy") End Function
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
