Syntax Error In My Dlookup Inside Qry

Dec 22, 2007

I wonder if you can help me. In my query, for this calculated field, i get an error message about a syntax error missing operator in query expression.
what did i do wrong? thanks

FormulaID is a text field.

UnitRM Combined: nz(DLookUp("UnitRM Combined","QryFormulaSummed","[QryFormulaSummed].[FormulaID]='" & tblCostJunction.FormulaID & "'"))

View Replies


ADVERTISEMENT

DLookUp Syntax Error Help

Mar 26, 2008

Hello,

I have a table called "ShrinkWrapBinding" where I am supposed to look up a "Price" from. As you see in the code below, I calculate a "totalPackages" number which comes from values in a form. My problem is that when I try to use this value (totalPackages) on the DLookUp function (as seen in this code) it gives me a syntax error exactly on the totalPackages variable. Please note that I added single quotes to the 'totalPackages' variable when I use it on the DLookUp function, and have tried every possible combination (single quotes, double quotes) but still doesn't work. What is it that I'm doing wrong?

Code:
Dim totalPackages As Integer
Dim tempPrice As Double

totalPackages = Me.QuantityBooks1 / Me.ShrinkWrapQty

tempPrice = DLookup("[ShrinkWrapBinding]! [Price]", "ShrinkWrapBinding", "'totalPackages' >= [ShrinkWrapBinding]![RangeField]")


Thank you

View 8 Replies View Related

How To Use DLookup Syntax

Feb 20, 2014

I need to use a dlookup on a report to look up fields on another query, other than what the report uses.

How do i use the dlookup syntax?

I have a field on another query called "Average" and the query name is AverageResults. The report is called Totals.

View 9 Replies View Related

Syntax For Dlookup In Table

Jul 1, 2007

Hi all, instead of doing a dlookup via a query, i'd like to do a dlookup for price direct in a table where the criteria is the value in Text1 from Form1

outt = Nz(DLookup("[Price]", "Table1")) Where Product = ' Text1' from Form1

Whats the correct syntax for this please? Thanks

View 2 Replies View Related

DLookup Syntax Problem

Jun 7, 2006

I am trying to implement DLookup for the following sql to a linked SQL Server table to verify that the currentuser is in my Traders table:

select tr_username from Traders where tr_username='edobbie'

When I enter the following DLookup command I get the error 'compile error expected ='

The offending line is:

DLookup("tr_username","Traders","[tr_username] = " &CurrentUser())

The Editor doesn't seem to see the = sign in my criteria. I've modified the syntax a number of ways to no avail.

Does anyone see the problem?

View 2 Replies View Related

Modules & VBA :: LIKE In DLookup After Syntax

Nov 5, 2013

I am looking to use a "If" statement with DLookup to find a record and see if the field name begins with "TW" as the characters. Here is what I have so far:

Code:

Dim matchCriteria As String
matchCriteria = "LIKE 'TW*'"
If DLookup("end_user", "tbl_module_repairs", "prikey = " & Me.txt_rid1.Value) = matchCriteria Then
MsgBox "Success"
Else
MsgBox "FAILURE"
End If

I am trying to switch between two sets of pricing for customers that begin with "TW" and then all else. Would the use of a "case" statement be better? If so I do not have the "end_user" bound to the form so I would need to embed Dlookup in the "case" method.

View 5 Replies View Related

DLookup Syntax Problem In Report

Jan 3, 2007

I have a report that when run, prompts a user for an id. The user enters and id and it pulls the information based on that id. The information is being pulled from data stored in Table A. I also have Table B that associates an id with a name. In my report I have the input being stored as a string, called strCampusId. I have a text box with called txtCampusMinistryName. I want the report to do a lookup based on the users input and display the CampusId Name, however I am getting an error. The error says, RunTime Error 3075, Syntax error (missing operator) in query expression 'ParishId=*0101'.


Table B (called CampusMinistry) only has 2 columns, ParishId and ParishName

Here is my code that I am trying to use.

txtCampusMinistryName = DLookup("ParishName", "CampusMinistry", "ParishId=" & strCampusId)

Thanks for any and all help.

View 2 Replies View Related

Modules & VBA :: Syntax With Filter In DLookup

Feb 26, 2014

If I type 2014 instead of b everything works out.

Code:

Dim a As Double
Dim b As String
b = Right(RepMonth, 4)
a = DLookup("[EUR/TRY]", "Plan_FX_RATES", "YEAR = '" & b & "'")
Debug.Print a
Debug.Print b
Debug.Print RepMonth

View 4 Replies View Related

Forms :: Multiple DLookup Syntax

Feb 24, 2015

Me.txtversion = "Ver:" & " " & DLookup("[txtVersionMajor] & " - " & [txtVersionMinor] & " - " & [txtVersionRevision]", "tblVersionInfo")

What's wrong with my syntax ? It's the "-" part that is the problem.

View 3 Replies View Related

Forms :: Date Field Syntax For DLookup

Apr 11, 2014

I have table [table1] which have 4 fields

EMPID - primary key
EMPFirstname
EMPLastname
EMPDOB

Input box as txtempid as string

I am trying to learn how I to display other field based on one field in my case last three field based on EMPID

I am actually getting syntax error when I dry to display DATE Field

Following are my attempts

Dim Verfirstname as string
Dim Verlastname as string
Dim VerDOB as date

Verfirstname = dlookup("[EMPfirstname]","table1","[EMPID]='" & me.txtempid & "'")
Msgbox Verfirstname

works fine. But when I use for Date, I am getting syntax error

VerDOB = dlookup("[EMPDOB]","table1","[EMPID]='" & me.txtempid & "'")
Msgbox VerDOB ---- now I am getting syntax error

I tried to understand over net and I believe i need to used with "#" before and after the date field but my criteria...

View 1 Replies View Related

Queries :: DLookup - Combine Multiple Criteria Syntax In Query

Apr 17, 2013

I am trying to create a Dlookup in Access 2010 within a query using query wizard. I want to lookup the tax rate for an employee based on a salary range and their 'tax category' (string). Through troubleshooting I can get the criteria to work separately.

These are: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "'")

DLookUp("Base","TABWT",[grosspay] & " Between [TABWT]![Minimum] And [TABWT]![MaxBracket]")

These work and return the correct values for each column/row when I run the query.

However, when I combine the criteria (using the build wizard) as follows:

Expr1: DLookUp("Base","TABWT","[TABWT].[Taxclass] = '" & [FirstOfTaxGroup] & "'" and [grosspay] & " Between [TABWT]![Minimum] And [TABWT]![MaxBracket]")

The Dlookup will returns 0 values but will not give an error message.

I've tried quite a few variations on syntax and quotes and so on. However, it's just not working for me.

View 3 Replies View Related

Error - CreateRecord Cannot Be Used Inside Of A ForEachRecord

Nov 13, 2013

I am trying to create several new records based on records in another table using the for each record loop in a Data Macro (After insert) but i keep getting this error:

CreateRecord cannot be used inside of a ForEachRecord

I basically have 3 tables.

Projects
Questions
Projects_to_questions

When a new project is added it is supposed to create an entry in the projects_to_questions table for each question in the questions table linked to the project. The user then fills in some extra fields for each question.

So is there another way to make Access auto create these entries based on the questions in the questions table?

View 2 Replies View Related

Modules & VBA :: How To Access Description Inside Form Error Event

Jul 31, 2013

I'm trying to extract information from Err.Description within a form's Error event. Alas, Err.Description does not seem to be available from there.I have a form that normally displays in DataSheet view. If a trigger on the Oracle back-end raises an error, I want my Access app to be able to parse out Err.Description and deliver a more user-friendly message than ODBC's message. I want to get the info about the message from the error coming back from Oracle, not by matching up error codes.

If I change the form to Single-Form view and put a Save button on it, I can capture Err.Description in the command button's Clicked() event, and then parse out what I need.So if I must display this form in Datasheet view, where can I trap the ODBC error and display my user-friendly message?

View 5 Replies View Related

Syntax Error

Mar 2, 2007

On my form I have 2 radio buttons rdoAll and rdoSpecific. If rdoAll is true then it prints a report. THis part works fine. However if rdoSpecific is true then I make visable combo box to look up an ID. Then when I click the button I want the same form to open but with just the info pertaining to the ID selected. Here is my code:

Private Sub cmdLotHistory_Click()

Dim stDocName As String, stSelection As String

stDocName = "rptLotHistory"

If Me.rdoAll = True Then
DoCmd.OpenReport "rptLotHistory", acViewPreview
End If

If Me.cboLotLU.Value > 0 Then
stSelection = "[LotID] =" & Me![cboLotLU]
End If

DoCmd.OpenReport stDocName, acViewPreview, , stSelection

End Sub

When I choose the ID and click the button I get the message

Run-Time error '3075':

Syntax error (missing operator) in query expression '(LotID
=020806B1585)'.

the 020806B1585 is my ID number.

Debug hightlights the
DoCmd.OpenReport stDocName, acViewPreview, , stSelection

but I'm guessing my real problem lies in the
stSelection = "[LotID] =" & Me![cboLotLU]


Can anyone point me in the right direction?

Thanks,
Rick

View 2 Replies View Related

Syntax Error

Sep 12, 2005

I have a function that builds a filter and it get a syntex error. It has three components and I must be missing something in combining them into the filter. SpecID and ReviewID are numbers. Selected is a checkbox and 'Yes' is a string. Can anyone see the source of the Syntax Error?

Private Function PlanFilter()
Dim strFilter1 As String, strFilter2 As String, strFilter3 As String
strFilter1 = "[SpecID] = " & [Forms]![frmMainEntry]![SpecID]
strFilter2 = "[ReviewID] = " & [Forms]![frmMainEntry].Form!fctlReviewRequests!ReviewID
strFilter3 = "[Selected] = 'Yes'"
gstrFilter = strFilter1 & " And " & strFilter2 & " And " & strFilter3 & ";"
Debug.Print gstrFilter
End Function

Thanks,
PC

View 3 Replies View Related

Syntax Error

Jun 20, 2007

I'm running a VBA query in excel trying to import the field from a table, simple so I thought, any idea on why i'm getting syntax error codes on this part?

Application.StatusBar = "Retrieving Depot Names"
sSQL = "SELECT tbldepot.DepotName 'Depot' " & vbCr & _
"FROM tbldepot, " & vbCr & _
"GROUP BY tbldepot.DepotName "
getSQLintoRange sSQL, Cells(1, iCol), True, True
iCol = iCol + 2


same thing on

'-= DFE =-
'Some manual DFEs have auth code but left at created
'AAP children have the DFE, not parent
Application.StatusBar = "Retrieving DFE Values"
sSQL = "SELECT tbldepot.DepotName 'Depot', " & vbCr & _
" SUM (di.Qty * di.Rate) 'DFEVal' " & vbCr & _
"FROM tblA537 a, tblA537DFE d, tblA537DFEItem di, tblDepot " & vbCr & _
"WHERE tblDepot.DepotID = a.DepotID " & vbCr & _
"AND a.OrderNumber = d.OrderNumber AND d.DFEID = di.DFEID " & vbCr & _
"AND NOT d.Authorisation IS NULL AND d.DFEStatus<>4 " & vbCr & _
"GROUP BY tblDepot.DepotName " & vbCr & _
"ORDER tblDepot.DepotName "
getSQLintoRange sSQL, Cells(1, iCol), True, True
iCol = iCol + 2

View 5 Replies View Related

Any Error With This Syntax?

Jun 26, 2007

Hi All,

As I am really 'Green' in this line, can anyone tell me what wrong with my syntax?

Thanks in advance.


INSERT INTO Depreciation (AssetID,DepreciationAmount,DepreciationDate)
SELECT Assets.AssetID,
CASE WHEN Assets.AssetID FROM Assets not in (SELECT Depreciation.AssetID FROM Depreciation) THEN Month(Assets.DateAcquired)*SLN(Assets.BookValue,As sets.SalvageValue,Assets.DepreciableLife)/12
ELSE SLN(Assets.BookValue,Assets.SalvageValue,Assets.De preciableLife)/12
END,
Format(Me!DepnRunDT, "0")
FROM Status INNER JOIN Assets ON Status.StatusID=Assets.StatusID
WHERE ((Assets.StatusID)=1);

View 1 Replies View Related

Syntax Error

Sep 20, 2007

I am trying to create a query using the SQL view, i get a missing operator error while running this sql

SELECT tblPDInv_Temp.CustId, tblPDInv_Temp.CustName, tblPDInv_Temp.OnHold, tblPDInv_Temp.InvID, tblPDInv_Temp.InvAmt, " & _
"tblPDInv_Temp.AmtPaid, tblPDInv_Temp.DueDate, [InvAmt]-[AmtPaid] AS AmtDue, tblPDInv_Temp.InvStat " & _
"FROM tblPDInv_Temp " & _
"WHERE ((([InvAmt] - [AmtPaid]) <> 0) And ((Date() - [DueDate]) > 0)) " & _
"ORDER BY tblPDInv_Temp.CustName, tblPDInv_Temp.CustID, tblPDInv_Temp.InvID;"

Thanks

View 2 Replies View Related

Syntax Error

May 5, 2005

I use the following code in the After Update Event of the field Lastname to check for a possible duplicate record:

If DCount("*", "[tblPeople]", "[LastName]= '" & Me.Lastname & "' And [PODate] = #" & Me.PODate & "#") Then
......

And it works well.
In case an existing record is found, I want to jump to it.
I tried using the following code, but it gives a syntax error

Me.RecordsetClone.FindFirst "[Lastname] = '" & Me![LastName] & "' And [PODate] = #" & Me.PoDate & "#"
Me.Bookmark = Me.RecordsetClone.Bookmark

Can't figure out what is wrong here.
Any help will be appreciated.

View 4 Replies View Related

Syntax Error?!

Mar 9, 2005

can someone help me here? i'm trying to get a few selected employee from two tables, namely EmployeeParticulars and ProjectAllocation. EmployeeParticulars contained all the employees in the company whereas ProjectAllocation has two fields (PID - the project and EmpID - the employee who is allocated). One project consist of many employees.


Ok, let's move on to my problem now. I'm trying to get those employees who are not involved in a current project. i tried this

Code: Select Emp.Name from EmployeeParticulars Emp, ProjectAllocation Pro where Emp.EmpID <> Pro.EmpID

but it doesnt work. instead in returns me all the employees in the EmployeeParticulars table and on top of that, each employee appears 21 times!(this 21 i believe is from the 21 employees for this particular project) So, it actually runs 21*27(no of employees) = 546 times!

i was supposed to use != but i realise it doesnt not work in MS Access so i used this <> instead. however it does not give me what i want. Can someone assist me please.

I hope I've made my question comprehensive.

View 3 Replies View Related

Syntax Error?

Mar 19, 2005

insert into date (ReqNum, date) values( 1 , '2005-03-20' )

I'm getting a syntax error with that. Does anyone know why?

View 1 Replies View Related

Syntax Error

Jul 6, 2005

Figured it out thanks.

View 1 Replies View Related

Need Help With Syntax Error

Aug 8, 2006

with my code I am having a syntax problem and I am new to asp so I am a little unsure of what needs to be changed.

Please help...

My error Message:
Code:Microsoft VBScript compilation error '800a03ea'Syntax error/addpicks2.asp, line 32values ('" & _username & "', '" & game1 & "', '" & game2 & "', '" & game3 & "', '" & game4 & "','" & game5 & "','" & game6 & "','" & game7 & "','" & game8 & "','" & game9 & "','" & game10 & "','" & game11 & "','" & game12 & "','" & game13 & "','" & game14 & "','" & game15 & "','" & game16 & "', #" & now() & "#)"--------^


My page code:
Code:<%' Declaring variablesDim username, game1, game2, game3, game4, game5, game6, game7, game8, game9, game10, game11, game12, game13, game14, game15, game16, data_source, con, sql_insert' A Function to check if some field entered by user is emptyFunction ChkString(string)If string = "" Then string = " "ChkString = Replace(string, "'", "''")End Function' Receiving values from Formusername = ChkString(Request.Form("username"))game1 = ChkString(Request.Form("game1"))game2 = ChkString(Request.Form("game2"))game3 = ChkString(Request.Form("game3"))game4 = ChkString(Request.Form("game4"))game5 = ChkString(Request.Form("game5"))game6 = ChkString(Request.Form("game6"))game7 = ChkString(Request.Form("game7"))game8 = ChkString(Request.Form("game8"))game9 = ChkString(Request.Form("game9"))game10 = ChkString(Request.Form("game10"))game11 = ChkString(Request.Form("game11"))game12 = ChkString(Request.Form("game12"))game13 = ChkString(Request.Form("game13"))game14 = ChkString(Request.Form("game14"))game15 = ChkString(Request.Form("game15"))game16 = ChkString(Request.Form("game16"))data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Server.MapPath("access_db/picks.mdb")sql_insert = "insert into week1 (username, game1, game2, game3, game4, game5, game6, game7, game8, game9, game10, game11, game12, game13, game14, game15, game16, entered_on)"values ('" & _username & "', '" & game1 & "', '" & game2 & "', '" & game3 & "', '" & game4 & "','" & game5 & "','" & game6 & "','" & game7 & "','" & game8 & "','" & game9 & "','" & game10 & "','" & game11 & "','" & game12 & "','" & game13 & "','" & game14 & "','" & game15 & "','" & game16 & "', #" & now() & "#)"' Creating Connection Object and opening the databaseSet con = Server.CreateObject("ADODB.Connection")con.Open data_sourcecon.Execute sql_insert' Done. Close the connectioncon.CloseSet con = NothingResponse.Redirect("showweek1.asp")%>

Any help is appreciated, Thanks!

-Anthony

View 2 Replies View Related

INSERT INTO Syntax Error ???

Oct 30, 2005

Hello All,
I'm am writing an App in Java connecting to an MS Access database. I am now getting a syntax error on the following insert into statement:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
source = "jdbc:odbc:DKOperations";
connection = DriverManager.getConnection(source);
Statement stmt = connection.createStatement();
String CustInfoTable = "CUSTOMER_INFORMATION";
stmt.executeUpdate("INSERT INTO " + CustInfoTable + " CUSTOMER_FIRST_NAME VALUES " + CustFirstName);

This is the error:
An SQLException occurred: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

I cannot figure out what the syntax error is. Anyone have an insight on this for me? Most likely something easy that I am missing.


Thanks!

View 1 Replies View Related

Help On NotInList SQL Syntax Error Please..

May 13, 2007

Hi All, I'am using the following code to update a table (tbl_CDT) on a NotInList event of a cboBox (CDT) I keep getting a SQL syntax error message, can anyone help resolve the code please

Private Sub CDT_NotInList(NewData As String, Response As Integer)

On Error GoTo CDT_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The CDT " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add to the list now?" _
, vbQuestion + vbYesNo, "DentureBase")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tbl_CDT's([CDT_Name]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new CDT has been added to the list." _
, vbInformation, "DentureBase"
Response = acDataErrAdded
Else
MsgBox "Please choose a CDT from the list." _
, vbInformation, "DentureBase"
Response = acDataErrContinue
End If
CDT_NotInList_Exit:
Exit Sub
CDT_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume CDT_NotInList_Exit


End Sub
Thanks very much

View 9 Replies View Related

Spot The Syntax Error

Jun 27, 2007

Hi there!

My first post here, and I'm hoping some kind soul will be able to help me (as you can tell, I'm a kinda needy type!!) :o

I've got two completely separate databases, for both of which I need to be able to print a single form at a time. No problem there, a report and a bit of code solved that - but only for one of them! This is the successful one:

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName As String

stDocName = "Issues"
DoCmd.OpenReport stDocName, acNormal, , "Issue_No = Forms![Concessions]!Issue_No"

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

For my other database, I simply cut & paste the code, then changed the relevant document fields, as below:

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName As String

stDocName = "Change Proposal Print"
DoCmd.OpenReport stDocName, acNormal, , "Issue No = Forms![Change Proposals]!Issue No"

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

But when I try to run this, all I get every time is: "Syntax error (missing operator) in query expression '(Issue No = Forms![Change Proposals]!Issue No)'".

I can't for the life of me figure why one works but not the other. I've checked and treble-checked that I've got exactly the right document names. The report is "Change Proposal Print"
The form is "Change Proposals"
The form field criterion is "Issue No" (without an underscore in this one.)

What operator could possibly be missing in the second one, but not the first? Am I being very dense? Can anyone out there help? (Am I asking too many questions in a whiny voice?) :confused:

Cheers,

mike b :D

View 4 Replies View Related







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