Running Query in VB Code and Assigned to a button on a Form

[sonaljain123]

Hi, I am trying to run the following code in VB in MS Access to basically run a query and I am getting a syntax error saying

"Syntax error (missing operator) in query expression 'HD_GL_ACCT_BALANCE.DATEFROM HD_GL_ACCT_BALANCE INNER JOIN MAP_BALC_DESC ON HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO = MAP_BALC_DESC.BAL_IDWHERE(((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)= BALCFA) AND ((HD_GL_ACCT_BALANCE.DATE) In (" & getDateFilter & "))))"

My actual code is as follows and the same query works perfectly fine in SQL:

Private Sub Command25_Click()
Dim stDocName As String
Dim iQuery As String
Dim dbsCurrent As Database
Dim qryTest As QueryDef
On Error GoTo Err_Daily_Balance_Sheet_View_Click
iQuerySelect = "SELECT HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO AS Account_Number, MAP_BALC_DESC.BAL_DESC AS Description, HD_GL_ACCT_BALANCE.GL_BALANCE, HD_GL_ACCT_BALANCE.DATE"
iQueryFrom = "FROM HD_GL_ACCT_BALANCE INNER JOIN MAP_BALC_DESC ON HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO = MAP_BALC_DESC.BAL_ID"
iQueryWhere = "WHERE (((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)= BALCFA) AND ((HD_GL_ACCT_BALANCE.DATE) In (" & getDateFilter & "))))"
iQuery = iQuerySelect & iQueryFrom & iQueryWhere
Debug.Print iQuery
Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Daily_BS_View_Query")

Can anyone spot any error in the same? Thanks in advance.

 

[Taruz]

Hi..

Try adding a space..

iQuery = iQuerySelect & " " & iQueryFrom & " " & iQueryWhere

or...:


iQueryFrom = " FROM HD_GL..........BAL_ID"
iQueryWhere = " WHERE ............ATE) In (" & getDateFilter & "))))"

 

[sonaljain123]

I think that works at least in terms of its compiling now. But its asking to input a value for BALCFA in an input box. Would you know why that might be the case?

Thanks!

 

[sonaljain123]

Hey, I got that part...also I have modified the where clause of the query to add more "OR" criterion...so my querry now looks like the following:

iQuerySelect = " SELECT HD_GL_ACCT_BALANCE.DATE,HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO AS Account_Number, MAP_BALC_DESC.BAL_DESC AS Description, HD_GL_ACCT_BALANCE.GL_BALANCE"
iQueryFrom = " FROM HD_GL_ACCT_BALANCE INNER JOIN MAP_BALC_DESC ON HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO = MAP_BALC_DESC.BAL_ID"
iQueryWhere = " WHERE (((((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)= 'BALCFA') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)= 'BALREC') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALCNS') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALLSF') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALCOM') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALRMG') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALHEQ') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALCRC') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALDIR') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALIND') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALCON') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALLNL') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALINDHFS') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALHEQHFS') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALRMGHFS') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALCONHFS') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALCLS') OR (HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALCST') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALMGL') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALTCAI') OR ( "
(HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALCOMHFS') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALHFS') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALSEC') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALSTI') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALTOI') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALINV') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALSWP') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALERA') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALALL') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALGDW') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALPAE') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALAOT') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALTAS') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALPERS') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALPAC') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALDOT') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALPUB') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALNIB') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALCDU') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALCDO1') OR (
(HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)=BALCDO2') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALCDO') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALCDS') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALMMD') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALNSN') OR ((HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO)='BALSAVINGS')) AND ((HD_GL_ACCT_BALANCE.DATE) In (" & getDateFilter & "))))"
iQuery = iQuerySelect & iQueryFrom & iQueryWhere
Debug.Print iQuery

Beacuse my where statement is very long, it runs into multiple lines. So it is throwing an error saying " compile error: expected: line number or label or statement or end of statement"

How can I separate the where query into multiple lines?

Thanks

 

[Taruz]

Attention to color parts..

iQuerySelect = "SELECT HD_GL_ACCT_BALANCE.DATE,HD_GL_ACCT_BALANCE.GL_ACCO UNT_NO AS Account_Number, MAP_BALC_DESC.BAL_DESC AS Description, HD_GL_ACCT_BALANCE.GL_BALANCE"
iQueryFrom = " FROM HD_GL_ACCT_BALANCE INNER JOIN MAP_BALC_DESC ON HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO = MAP_BALC_DESC.BAL_ID"
iQueryWhere = " WHERE HD_GL_ACCT_BALANCE.GL_ACCOUNT_NO in ('BALCFA','BALREC','BALCNS','BALLSF','BALCOM','BALRMG','BALHEQ'),'BALCRC','BALDIR','BALIND','BALCON','BALLNL','BALINDHFS''BALHEQHFS','BALRMGHFS', [COLOR="Red"]" _[/COLOR]
    [COLOR="Red"]& "[/COLOR] 'BALCONHFS','BALCLS','BALCST','BALMGL','BALTCAI','BALCOMHFS','BALHFS','BALSEC','BALSTI','BALTOI','BALINV','BALSWP','BALERA','BALALL','BALGDW','BALPAE','BALAOT', [COLOR="red"]" _[/COLOR]
    [COLOR="red"]& " [/COLOR]'BALTAS','BALPERS','BALPAC','BALDOT','BALPUB','BALNIB','BALCDU','BALCDO1',BALCDO2','BALCDO','BALCDS','BALMMD','BALNSN','BALSAVINGS') [COLOR="red"]" _[/COLOR]
    [COLOR="red"]& "[/COLOR] AND HD_GL_ACCT_BALANCE.DATE In (" & getDateFilter & ")"
iQuery = iQuerySelect & iQueryFrom & iQueryWhere

in fact, so many "or" no need to use.

Instead, as the above "In" function use..

 

[sonaljain123]

Thanks!

Also is there anyway in MS Access to not to sort data alphabetically? So basically I am trying to create a view in MS access which is of course not alphabetical so I was wondering if I can create the same table in MS Access?

Thanks