Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

VBA Syntax

I am trying to kick off a report through VBA using a where clause which I can get to work for a numeric but I can't seem to get it to work for a literal.

Below is the syntax I am using for a numeric could anyone please tell me what I have to change for it to work with a literal ie "Marsh"

DoCmd.OpenReport stDocName, acPreview, , "[qryNotificationsByBroker]![BrokerAddressID] = 112"

I would be grateful for any help


Conor M Hamill

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Convert SQL Server SQL Syntax To MS Access Syntax
How can I convert SQL server SQL syntax to MS Access Syntax?
What is the differences, are there any conversion classes or modules I can
I never used any SQL functions, just SELECTing, INSERTing, DELETEing and UPDATEing
and just the SQL function to get the latest added row, but that is not really a problem, I'll should be able to sort that out.

Thanx in advance

Edited by - NLategan on 7/11/2004 9:15:21 AM

Any One Help Please ! VB Syntax Or Crystal Report Syntax Problem Help Please !
Hi friends,

 First I will say what I am trying to do.

     When I Choose the Itemname from two combo boxes
The report shows the Item details for the Items Between the two combo boxes.

There are two combo boxes CmbFrom and CmbTo
I am also passing the date here in the textbox txtfrom and txtto and assigning fdt=txtfrom.Text and tdt = txtto.Text
I face a problem, when I write the code like below. kindly clear me,

        dim sf as string
Private Sub cmdPreview_Click()

 sf = "{Stock.ItemCode} Between '" & cmbFrom.Text & "'and'" & cmbTo.Text & "'" & "AND" & "{Sales.InvDate} in Date(" & _
 Format(fdt, "YYYY,M,D") & ") to Date(" & _
 Format(tdt, "YYYY,M,D") & ")"

End Sub

it shows the error like this 'Error in formula <Record Selection>' "The remaning text doesnot appear to the part of formula".

instead of this when I write the code as below

Private Sub cmdPreview_Click()

 strsf = "{Stock.ItemCode} in'" & cmbFrom.Text & "' to '" & cmbTo.Text & "'" & "AND" & "{Sales.InvDate} in Date(" & _
 Format(fdt, "YYYY,M,D") & ") to Date(" & _
 Format(tdt, "YYYY,M,D") & ")"

End Sub

the report is showing the correct value whe I choose the very First Item in the cmbFrom combo and last Item in the cmbTo comb, but when I choose a Item in the middle order from CmbFrom and CmbTo combobox
it is not showing all the items between the two combo box. it shows only the one item which I choose in CmbFrom combo box. but i need to show all the Items in report between the two combo boxes.

Kindly correct me the code or suggest me how to execute it, or if there is any other way kidnly reply.

Thank you very much,
Thank you very much,


<B><font color="#008000">HAVE A NICE DAY !</font></B>

Edited by - chs on 2/5/2003 4:19:35 AM

Hi there,
Using a rich text box, how could I colour as you type kinda thing. I'm making a php script writter, so it would need to be for the php syntax colours.

So, how can I colour ; (end of line) as you type, and colour any thing after " untill you reach the next "?


A Little Syntax Help
What would be the right way to right this so it works?

If sock1.sock <> active Then
winsock.remotehost = "66.444.2.2.1
winsock.remoteport = 25
Blah blah
Like the top line is wrong. What is correct to find if the socket is already active?

VB Syntax
Hi, I'm new to VB and using Access

I want to be able to store values of the control (textbox) into a table using VB.

How to I go about it?

Vb Syntax
Hi there,
I'm new to vb6 programming and I'm creating a process using automation. I want to accomplish 2 things:

- Open a new document based on a customized template
- Insert a table(s) at a specific point within the new document.

Thanks Chuck Hartford

Is It Right Syntax?
Hi friends,
I try to have two nested block am I wright in syntax?


If StDev(Log(dblTest)) > 2 Then
dblTest = Log(dblTest) / Log(10)
If 0 < dblTest And dblTest < 1 Then
ElseIf dblTest > 1 Then
End If
End If

Help With Syntax Please
I am using the following command to set a variable to the value of a subform on for "laptop user".

Set MachineNames = "laptop users"!machine_name

I get this error:

"compile error: Expected end of statement."

What am I doing wrong, please?

Thanks in advance

PS: Please note the space in the form name. the complete parent/child location of the field is:


Need Some Syntax Help Please.
Hi I'm a novice at VB so if this question seems silly, please excuse me. I'm trying to program an excel macro for a datasheet download that I frequently use at work. Because the datasheet can vary in the number of columns and rows I need to program in relative formulas to account for this. At the beginning of the macro I use a counter to determine the number of rows and columns and assign those values to a variable.

ColCount = Selection.Columns.Count
RowCount = Selection.Rows.Count

I then want to use those values to calculate the relative formulas. Can anyone tell me the proper syntax for this? When I use the macro recorder I get something like this:

ActiveCell.FormulaR1C1 = "=R[-12]C/R14C"

I want to substitute the hard numbers in the formula with the values stored in the variables. Also how do I enter a placeholder ($) into the above formula? I have tried to play with it a bit and have tried to do a bit of research without much luck. Thanks for the help!

Syntax Help
hi all ,

i have a code like his Sql = Sql & "A1" where sql is defined as string
and if i have to replace it with "A2" but i want to make it take value from the i value in the for loop

for( i=1 to 3) for every loop i want it to show as A1 , A2 and A3

Better Syntax
Is there a better way to write this function? seems a bit long. It's adding cell values and setting it to a variable.

intSpotValue = Range(somecell).Value + Range(somecell1).Value + Range(somecell2).Value + Range(somecell3).Value + Range(somecell4).Value + Range(somecell5).Value


VBA Syntax?
I want to code a module below using 2 worksheets i.e "Data" and "Query" but not good in vba code.

Look in the datasheet at row2 for a value of 1, if found, then look at next row i.e. row3 for value found in the querysheet. If the individual value found is the same as in the querysheet individual column, it will write the date & rowno of row2 of datasheet and the value of 1 or 0 will be insert accordingly to the column into the querysheet.

This macro will run the loops from row2 of datasheet until the last row. New found items/values will be written row after row in the querysheet. Can anyone help.

Some questions concerning VBA syntax.

Q1) To use a workbook/worksheet
Q2) Do whil not eof() & Enddo.
Q3) Checking a range of cells for value
Q4) Skipping next row
Q5) Writing value to particular cell into particular cell
Q6) Do Case & Endcase with Select.

Can someone tell me what are these syntax in VBA.

Thank you.

Help With Syntax!
Is there a place where a Visual Basic user can get a list of all syntax? Whenever I go to work on a program I always run into code that I doní know how to use and I canít seem to find it listed anywhere and itís often just a simple matter of wording, ordering the syntax correctly.
I also have another question. Can anyone please explain how a variable, function, and statement are different? I use them all the time but what are they fundamentally?


Help With Some Syntax

If txtUsername <> Mid(c001A, 56, 1)
I am very familiar with programming in general (Experience with Java and C/C++. I've also done some VBA.). Now, I cannot find any good documentation for VB and I don't care for purchasing a book at this time.

Equal operator?

Mid(c001A, 56, 1)
c001A is a constant... and 56 and 1 look like bounds... what does this function return?

Any good documentation links would also be very helpful!



I don't want to update my screen any more than necessary to keep flicker to a minimum. I WANT to say something like...

If Form1.Picture1(VC).Picture <> Nothing then
Set Form1.Picture1(VC).Picture = Nothing


If Form1.Picture1(VC).Picture="C:Tape.GIF" then

Could someone tell me the correct syntax?


VB 6 SQL Syntax
Could you please help and advice on below SQL statement
When run compile an error message prompted syntax error on update

'Select Inpoint as Inp, GrPoint as GrP from Temp Table.
Then Update the selected record into AttSum,
where EmpNo in Attsum exist in Temp.

Dim DB As New ADODB.Connection

DB.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "Attendance.mdb;Persist Security Info=False")

sSQLstr = " Update AttSum Set (GrP, GnP) = (select InPoint as InP,GrPoint as GrP from temp) Where EmpNo in ( Select EmpNo from temp ) "
DB.Execute sSQLstr

Thank You

I am testing this code to create a textbox by code (so I can generate forms depending on information already gathered).

Private Sub Command1_Click()
CreateMyTextBoxControl ("Hello World!")
End Sub

Public Sub CreateMyTextBoxControl(test)
' Create a new TextBox control using this constructor.
Dim textBox1 As New TextBox()
' Assign a string of text to the new TextBox control.
textBox1.Text = test

' Code goes here to add the control to the form's control collection.
End Sub

I get a syntax error when I click on the command button with the following being the offending code:

Dim textBox1 As New TextBox()

I got the code from MSDN. Anyone know whats wrong with it?? Please

(VB6 Enterprise by the way)

SQL Syntax
I know there's a similar thread already up but i'm doing the SQL slightly different. I'm stuck with the syntax below it worked fine until I put the ORDER BY bit in, I didn't think I needed it at first but the query is not getting the data in the order I was expecting.

Set mrecScaling = g_DataConnection.Execute _
("SELECT * FROM [tblScalingFactors] WHERE [Year] =" & Me.TxtDataYear & "ORDER BY [VehCat]")

cheers and thanks for looking


Help With Syntax
I have a holiday table in my DB and have start_date and finish_date as 2 fields. I also have a date picker on one of my forms. If the datepicker.value is equal to or between the start and finish date in my table I wish for some text boxes to go red how would i go about coding this?????

I wish to say some thing like:

if dtpicker.value "= or between" adodc10.recordset![Start_date] and Adodc10![Finish_Date] then x
else y

Any Help would be great

Next Syntax
I am making a tile engine which is almost done. I get an error though that says "Invalid Next control varriable refference". I will post the code if the upload dosent work

tile(0) -image conrol array on from1 up to tile(7)
ground -image of ground

Private Sub Form_Load()
End Sub

Dim img As Integer ' Declare an integer indexing variable.
Dim nIndex As Integer ' Declare an integer indexing variable.
Sub Map()
Dim tile(1 To 4)
tile(1) = "1" 'Sets the picture Value
tile(2) = "1"
tile(3) = "1"
tile(4) = "1"
For nIndex = 1 To 4
For img = 1 To 4
If tile(nIndex) = 1 Then Form1.tile(img).Picture = Form1.ground 'Detrimes what picture to display
Next nIndex
Next img[b]Problem occurs[/b]
End Sub

As you can see it checks which array index holds "1" then changes the tile array to grounds picture. The problem is I need to to do this to each tile which is the reason for "img"

Sql Syntax
What is the proper syntax for this?

This works fine:
rs.Open "select horsepower from toshiba where (product='E3') and (volts = '230')", Conn, adOpenStatic, adLockOptimistic

but when I add variables, it doesn't work:
rs.Open "select horsepower from toshiba where (product=CBOPRODUCT.TEXT) and (volts = cbovolts.text)", Conn, adOpenStatic, adLockOptimistic

SQL Syntax
I'm using a Dataenviroment in an ActiveX object.

Here's my SQL statement:
SELECT TOP 1 c_user, c_pass, c_email FROM dbo.tblmailbox
WHERE (c_email = '[emailaddress]')

"emailaddress" is a parameter passed from the webpage
<PARAM NAME="emailaddress" VALUE="">
Also the default value within the VB code is ""
Const m_def_emailaddress = ""

Unfortunately, this SQL code doesn't work. I get the following error:

Run-time error '3021'

Either BOF or EOR is True, or the current record has been deleted.
Requested operation require a current record.

I guess I got a zero recordcount.

When I try to run the query with out an the varible it works.
SELECT c_user, c_pass, c_email FROM dbo.tblmailbox
WHERE (c_email = '')

So I figure either Im not setting my default parameter "emailaddress" correct or my SQL syntax is off.

Any advise whould be appreciated.

SQL Syntax

Overjoyed to discover this excellent forum. Hope someone might be able to help with the following.

I'm making a small VB application to allow non-access aware users to interogate a Acces database by select ing check boxes to indicate teir search criteria. The app is suppose to connect to the databse and run some SQL.

Mostly it's fine. But I'm having difficulty with some AND OR stuff: ( see below)

olA.DoCmd.RunSQL "SELECT [First Name], [Surname], [email], [selected], [Country], [Company],[ISHUB]" & _
"INTO contacts From [CONTACT DATA]" & _
"WHERE" & _
"[ISHUB]= '" & Text1.Text & "' AND [IT] ='" & Text16.Text & "' Or [TELCOM] ='" & Text17.Text & "' Or" & _
"[ISHUB]= '" & Text2.Text & "' AND [IT] ='" & Text16.Text & "' Or [TELCOM] ='" & Text17.Text & "' Or" & _
"[ISHUB]= '" & Text3.Text & "' AND [IT] ='" & Text16.Text & "' Or [TELCOM] ='" & Text17.Text & "' Or" & _

etc etc etc

Would be grateful for any help

thanx in advance

SQL Syntax
I'm having a little trouble getting the syntax correct on this sql statement...

Like '" & varButton & "%' Order By Lastname, Firstname", Conn
What I'm trying to do is have a variable followed by a wildcharacter in the where clause of the sql statement. Any help on syntax would be greatly appreciated. Thanks!

Bad SQL Syntax?
Ok, now Im running into the same problem. I know the variable prstfldCategory is getting passed to the procedure correctly, but not to the Recordset command. Do I have bad syntax again?

Private Sub InitTreePartName(pnodParent As Node, _
pstrfldCategory As String)

Dim pnodCurrent As Node
Dim prstCurrent As ADODB.Recordset
mcmdCurrent.CommandType = adCmdText
mcmdCurrent.CommandText = _
"SELECT DISTINCT fldSubCategory FROM tblInventory " &amp; _
" WHERE fldCategory = " &amp; pstrfldCategory &amp; ""
Set prstCurrent = mcmdCurrent.Execute

Help With Syntax
hi folks, i got to do some reverse engennering on same reports in access

can some help me figure out what this means


it's the "RECOM: " i can't figure out?

I'll be among the best soon, very soon!!!

Help On SQL Syntax
Dim rs As New ADODB.Recordset, sql As String
sql = "SELECT Annee FROM HiverInfo ORDER BY Annee"
rs.Open sql, sConn, adOpenForwardOnly, adLockReadOnly, adCmdText
Do While Not rs.EOF
cmbAnnee.AddItem rs!Annee
Set rs = Nothing

Hi is there an SQL syntaxt to make sure it only give unique value...
i'm searching in a 7000 database and there a few date like 2003, 2004 and 2005 is there a way to say
sql = "SELECT Annee FROM HiverInfo 'NO DUPLICATE' ORDER BY Annee"

SQL Syntax In VB 6
First post

I know there's about a billion questions exactly like this and 4 tutorials linked on these ADO connections but I can't find the answer to my question in any of those places.

I'm trying to do an sql query with 2 long integer datatypes and I keep getting data mismatch or missing parameters or etc... it isn't working. and I know it's the sql statement because if I edit it without the "WHERE" condition, it works fine.

strAcntSQL = "SELECT * FROM Account WHERE [UserID] = '" + lngAccount + "'"

that's the query. Yes lngAccount is declared and a value is set. UserID is a long integer datatype as well

Also, if the syntx for other datatypes such as integers, double, boolean, etc, could also be listed, it'd really be a big help as the only one I can get to work right now is string '" & strValue & ""

Any help would be greatly appreciated.

Using The BETWEEN SQL Syntax
Hi there! I have a problem using the BETWEEN SQL syntax in my VB application. I'm using SQL server and this is my code:

Dim SQL As String


SQL = "SELECT * FROM InOut WHERE TransDate BETWEEN '21/06/2005 AND '20/07/2005'"

Set ListRS = New ADODB.Recordset
ListRS.Open SQL, DB

Do While Not ListRS.EOF
Set ItemX = ListView1.ListItems.Add(, , ListRS!TransNo)

ItemX.SubItems(1) = ListRS!EmpNo
ItemX.SubItems(2) = ListRS!EmpName
ItemX.SubItems(3) = ListRS!TransDate
ItemX.SubItems(4) = ListRS!ScanIn
ItemX.SubItems(5) = ListRS!ScanOut
ItemX.SubItems(6) = ListRS!BasicHours
ItemX.SubItems(7) = ListRS!Overtime
ItemX.SubItems(8) = ListRS!TotalHours
ItemX.SubItems(9) = ListRS!Remarks


and the records I needed doesn't show . My connection string is perfect. in fact, if I use the syntax "SELECT * FROM InOut WHERE EmpNo = ' somestring '" it works fine except when I use the BETWEEN statement. Please help...

SQL Syntax?!&gt;
Is there anything wrong w/ the syntax below? When using an Access DB it works fine, but with MSDE i get the following error:

Run-time error '-2147217900....
"Incorrect syntax near the Keyword 'INNER'.

MatCN.Execute "UPDATE MatTran INNER JOIN MatOnHand ON " & _
MatTran.MatNo = MatOnHand.MatNo SET MatTran.BillPO = '" & _
PONum & "', MatTran.BillDate = '" & Date & "', MatTran.Billed = 1 " & _
WHERE MatOnHand.Vendor='" & vendor & "'"

SQL Syntax Help
I have two tables i'm dealing with: tblProducts and tblReserved.

I want to populate a drop down control with product models that are in stock but, that are not reserved. tblProducts primary key: prod_id is the foriegn key in tblReserved.

this is what I have tried.

Dim strSQL As String

strSQL = "SELECT TP.prod_model FROM tblProducts TP INNER JOIN tblReserved TR ON TP.prod_id = TR.prod_id" _
& " WHERE TP.units_in_stock > 0 AND TP.brand_id = " & Val(txtBrandID)

P.S. I'm only looking for the correct SQL not how to populate a drop down.

SQL Syntax Help Please
Hi All

Please could someone help me with this SQL syntax please, it is for a datareport and i am trying to print the report according to 2 criteria, it all works fine with only 1 criteria but the moment i start the AND Rest_Location i get a syntax error message, and can't seem to spot the problem.

Private Sub DataReport_Initialize()
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim sSQL As String
Set MyConn = New ADODB.Connection
Set MyRecSet = New ADODB.Recordset
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:quick-resdatadata.mdb;"
sSQL = "SELECT [Customer Name],[Table Number],[Covers],[Arrival Time],[Area],[Booking By],Rest_Location FROM Daily_Res Where [Arrival Date] = #" & Format(Date, "d/m/yyyy") & "#" & "' AND Rest_Location = '" & frmQuestion.Text1.text & " '"

MyRecSet.Open sSQL, MyConn, adOpenStatic, adLockReadOnly

Set rptArrivals.DataSource = MyRecSet

End Sub
Many thanks for any advice

Can someone please give me the syntax for this update statement?
"update work_order set order_status= '" & txtStatus & "' where row_num=" & Combo1
This gives me and 'Arguments are of the wrong type, are out of acceptable range, or are in confilct with one another' error.

Help With Syntax
Can you tell me what is wrong with the syntax in the update statement?

Private Sub updateUser()
Dim rs As DAO.Recordset

db.Execute "UPDATE userfile SET userfile.expDate = DateAdd('yyyy', 1, now);" WHERE relation='AP'", " And coNum=" & comp, dbFailOnError

End Sub

Sql Syntax...
sSQL = "SELECT DISTINCTROW ID, PM, FROM tblInstall WHERE PM = true and ID= '" & Environ("USERNAME") & "';"

is pm = true written correctly

What is the syntax of mciSendString function. What are it's arguments and their description.

Imran Ahmad Mughal

Asking Some Syntax
Hi All,
Hope can help from the list. i think should be quite fundamental to most of you. really hope can get the confirmation from the list.
thanks alot

1)what is the difference for a and b below? i know both in modules and for b, in order to execute must have a CALL. what about a which is sub main()?

a) sub main()
end sub

b) public sub AutoSelect()
end sub

2)if the variable to able to use through out the project i must declare as public in modules declaration. which means even in module declaration, i also need to put public?

3)can i have a public variable decared in any procedure also?

4)if 3 can, means the variable is only public to a particular form only?

5)private function means the function only can use in all the private sub in a particular form?

6)parameter in a function i always can see by val. What is By Val?

PHP Syntax To VB 6.0 Syntax

Can any help to translate this php script in VB 6.0 Style, Php Code is below

if ($part4 <= -1 || $part4 >= 1)
return 99;

if ($astro->ra[1] > 350 && $astro->ra[2] < 10)
$ra2 += 360;

how to translate this coding in visual basic 6.0

ware regards

Help Me Please On Syntax ...

Can somebody help me please educate me on how to improve this vb statement :

Data1.Recordset.FindFirst "Codeno = " & content ?

I have this task of verifying a record on the database whether it exist or not. Is there such as Data1.Recorset.FindFirst Field("Codeno.value") = & Content ?

VB Code:
Public Sub cmdAddEntry_Click()       content = Trim(txtActivityCode.Text)       Data1.Recordset.MoveFirst       Data1.Recordset.FindFirst "Codeno = " & content       If Data1.Recordset.EOF Then           'Add record       Else          'Beep , record exists       End If   End Sub

Thank you in advance,


SQL Syntax
I can't seem to figure out the correct syntax for the AND statement. Could someone help out?

VB Code:
strSQL = "SELECT [Reason for update]FROM [Grade standard - Main] WHERE [Grade ID]= '" & strGradeID & "'" _     and [Date approved]= '" & cboApproved.Text & "'  

SQL Syntax In VB Help Please
Hi, helping a fellow student with their project and have hit a slight hitch. Basically I'm altering a report's recordsource to an SQL statement. This is no big deal, however, the SQL statement itself is a doozy! I couldn't code it in SQL myself so I used the Access query builder to create the query then switched to SQL mode, copied and pasted. However, when I run my code when it tries to set the recordsource of the report to the SQL statement I keep getting a syntax error referring to the SQL statement. The code is below:

strSQL = "SELECT tblCSCase.CaseNo, tblCSSelection.DateServiceDue, tblCSServiceProvider.ServiceProviderFirstName_Initials, tblCSServiceProvider.ServiceProviderSurname, tblCSSelection.PointInstructedOn" & _
"FROM tblCSServiceProvider INNER JOIN (tblCSCase INNER JOIN tblCSSelection ON tblCSCase.UniqueNumber = tblCSSelection.UniqueNumber) ON tblCSServiceProvider.ServiceProviderID = tblCSCase.ServiceProviderID" & _
"WHERE tblCSSelection.PointInstructedOn=strFirst;"

Could some kind soul have a look at this please? I've never done InnerJoins in my SQL as I'm just learning it and I bet that is where the problem lies.

I know the tables and fields have very lengthy names. She's learnt the error of her ways

Thanks a lot, Lol

RTB Syntax
Does anyone know where I can find some of the actual rtb syntax ( for changing color and stuff, not seltext). Thanks.

TCP Syntax
hi all,

iam a newbie to TCP client prigramming and Iam trying to insert .txt file to a server location using TCP client. I am getting error when iam trying to work like this. Can anyone tell me how i can make this work .

Text2.Text = "readme.txt"
tcpClient.SendData Text2.Text

SQL Syntax
I have an MSAccess file where I am retrieving recordsets for "Recipe part (1 to 5) starch". I'm using a For... Next loop. The problem is I can't figure out how to identify "starch" in the following.
For i = 1 To 5
strSQL = strSQL & "[Recipe part " & i & " ] , " what's the correct syntax to identify "starch " here?

I have come across two different syntax's in the past couple of days. Both seem rather odd. Examples are ...

Using VBAccelerators SGRID. Why := and not just =

VB Code:
.AddColumn "Categories", "Categories", lColumnWidth:=48

The second:- What happened to /? why use .

VB Code:
y Screen.TwipsPerPixelY

The latter I have come across before but cannot remember the answer. The former is a new one.

Can anyone shine any light on this for me?

Thanks in advance.

Please Help On Sql Syntax.
Hi Guys

How are you all doing?

I have a question about a certain sql syntax. I have been using vb for 2 months and I'm still learning as i go...

The thing I want to achieve is to create a vb script to hit a database and dump the data in my excel spreadsheet.

I have figured out a way to dump the results via record

The problem is, i have 2 different database to select from.
The code looks like this(this one works):

.CommandText = Array( _
"SELECT FS.ActDeptTime, FS.MultiDepCode, FS.AircraftTail," _
& "FS.EstDeptTime, SSV.SchArrvTime" _
& Chr(13) & "" & Chr(10) & "FROM QA_PxREZ.dbo.Aircraft A" _
& Chr(13) & "" & Chr(10) & "RIGHT OUTER JOIN " _
, _
"QA_PxREZ.dbo.FlightSegment FS INNER JOIN " _
, _
"QA_PxAPP.dbo.view_dms_SchSegment SSV ON FS.FlightSegmentID = SSV.SchSegmentID INNER JOIN QA_PxAPP.dbo.internal_view_dms_SegmentCabinMaxDate SC INNER JOIN " _
, _
"QA_PxREZ.dbo.Segment SMT ON SC.SegmentID = SMT.SegmentID ON SSV.SegmentID = SMT.SegmentID ON A.AircraftTail = FS.AircraftTail LEFT OUTER JOIN " _
, _
"QA_PxREZ.dbo.MarketType MT ON FS.MarketTypeID = MT.MarketTypeID LEFT OUTER JOIN QA_PxREZ.dbo.FlightState FST ON FS.FlightStateID = FST.FlightStateID LEFT OUTER JOIN " _
, _
"QA_PxREZ.dbo.FlightType FT ON FS.FlightTypeID = FT.FlightTypeID LEFT OUTER JOIN QA_PxREZ.dbo.FlightClass FC ON FS.FlightClassID = FC.FlightClassID" _

From this sentence

"SELECT FS.ActDeptTime, FS.MultiDepCode, FS.AircraftTail," _
& "FS.EstDeptTime, SSV.SchArrvTime" _

SSV belongs to a different database. But this statement works.

If I add on to this statement, like this:

"SELECT FS.ActDeptTime, FS.MultiDepCode, FS.AircraftTail," _
& "FS.EstDeptTime, SSV.SchArrvTime", "SSV.ActArrvTime" _

I get a "type mismatch" error.

Any ideas what or how to get by this problem?

Thank you very much and hope to hear your suggestions.

Syntax Help?
I need help with a SQL statement.
I need to update TWO tables in one go! The two tables are called tbl_ManifestDet and tbl_Accounts.

Here is my statement below. Currently it updates my first table called tbl_ ManifestDet with a dexcode and AccountNo. I need to update the field called "manifested" & set it to "N" in another table called tbl_Accounts. How do i write this?

VB Code:
strSQL = "UPDATE tbl_ManifestsDet SET dexcode = '" & reason & "', manifested = 'N' WHERE AccountNo = '" & Mem(SockNr) & "'"            adoConn.Execute strSQL

Help With Syntax
This is my first time at VB Forums.
I think this is the correct area for posting coding questions.
If it is not, please let me know.

I have been building a VB6 application with Jet 4 Microsoft Data Base for tracking investments.
Since I am relatively new to VB I continue to have difficulty with the syntax.

The following code generates a syntax error when I run the application.

adoTrackInvest.RecordSource = "SELECT Invested FROM History WHERE Date >= # " & txtTrackingDate & " # AND Investor_Number = " & frmStartPage.txtInvestorNumber & " AND Value = " & txtHiVal & "'"

What is puzzeling to me is that the Error Message Box that warns of the syntax error seems to be able to draw the required info from the text boxes.

The message I get is:
Syntax error in query expression 'Date>= #4/3/2004# AND Investor_Number = 5 AND Value = $29,697.00".

All these values are correct.

Any help on this will be greatly appreciated.

SQL Syntax
select * from table1 where col1 in (a subquery)

I get error if the subqury return no value

this case is only in ACCESS not in MSSQL.

I need a solution

Copyright © 2005-08, All rights reserved