'Using Excel As Your Database'

[Conrado Natac]

Hi Conrado ,

You are right. When in doubt , always try with literal values instead of parameters ; when you use parameters , the SQL string should be the same as when you use literals.

Thus , when you try in your specific case , the normal SQL string would be :

SELECT * FROM [data$] WHERE [data$].[Type] = 'I' and [data$].[Cust #]='GL 888'

Now , when you use parameters , you should see that your strSQL after its assignment is identical to the above. Only then will your query work correctly.

Since the variable xtype contains the character I , the single quotes on either side have to be added. Similarly for the variable cmbcustno.Text

Narayan

Thanks for the the clarification. It certainly help me as a novice. Now, I understand where you are coming from.

Wow, I am learning more from just reading here and using your aadvice.


Thanks

 

[Conrado Natac]

Hi Conrado ,

You are right. When in doubt , always try with literal values instead of parameters ; when you use parameters , the SQL string should be the same as when you use literals.

Thus , when you try in your specific case , the normal SQL string would be :

SELECT * FROM [data$] WHERE [data$].[Type] = 'I' and [data$].[Cust #]='GL 888'

Now , when you use parameters , you should see that your strSQL after its assignment is identical to the above. Only then will your query work correctly.

Since the variable xtype contains the character I , the single quotes on either side have to be added. Similarly for the variable cmbcustno.Text

Narayan

Thanks for the the clarification. It certainly help me as a novice. Now, I understand where you are coming from.

Wow, I am learning more from just reading here and using your advice.


Thanks

I kept re-reading your notes, now it is clear to me.

 

[Conrado Natac]

I kept re-reading your notes, now it is clear to me.

Narayan,

I tried my best on this one, but could not figure out what is wrong with this code


rs.Open "SELECT [Date],[Due Date], [Cust Name], Distinct [Apply to], sum[Amount] as InvBal FROM [data$] WHERE [data$].[Cust #]=" & "'" & cmbcustno.Text & "'", _
cnn, adOpenKeyset, adLockOptimistic


"it keeps giving me the error code Syntax Error( missing operator) in query expression "distinct[Apply to ]"

I truly appreciate the effort of helping me understand this. I feel that I am learning at a fast rate by simply reading the thread and the example you provided.

 

[NARAYANK991]

Hi Conrado ,

The DISTINCT keyword cannot be preceded by anything else ; you can have SELECT statement such as :

strSQL = "SELECT DISTINCT [Apply to] FROM [data$]"

strSQL = "SELECT DISTINCT [Apply to] from [data$] WHERE [data$].[Cust #] =" & "'" & cmbcustno.Text & "'"

If you wish to have multiple fields output for one DISTINCT field , then you need to have sub-queries with the query. Or you need to use the GROUP BY construct.

Can you explain in plain English what are the output fields you want printed out to the view tab ?

Narayan

 

[Conrado Natac]

Hi Conrado ,

The DISTINCT keyword cannot be preceded by anything else ; you can have SELECT statement such as :

strSQL = "SELECT DISTINCT [Apply to] FROM [data$]"

strSQL = "SELECT DISTINCT [Apply to] from [data$] WHERE [data$].[Cust #] =" & "'" & cmbcustno.Text & "'"

If you wish to have multiple fields output for one DISTINCT field , then you need to have sub-queries with the query. Or you need to use the GROUP BY construct.

Can you explain in plain English what are the output fields you want printed out to the view tab ?

Narayan

Thanks for your patience.

Hey Narayan,

What I would like to see is the balance of individual invoices per customer as of a certain date.

The header will be the the date, Invoic e number, Invoice Amount , Invoice balance

The amount of the invoice is on the amount field with a type of “I”

The sum of amount using the customer number and the apply to as reference will give you the balance of the invoice. This is the reason for sum command that I could not find to work

In this example for customer A200, the balance is invoice 112 for $112.00

I need to be able to put a variable that will filter it as of certain cut off date. Sometimes, you want to know the balance from last month, last year, etc.

You can see my sample on the view tab.

Incidentally, how can I put a variable field for date, where I can put the cut off date.

I find this discussion so interesting that I keep reading your postings.

Thanks a lot for your kind and patient support for novice like me.

Conrad

 

[Conrado Natac]

Hi Conrado ,

You are right. When in doubt , always try with literal values instead of parameters ; when you use parameters , the SQL string should be the same as when you use literals.

Thus , when you try in your specific case , the normal SQL string would be :

SELECT * FROM [data$] WHERE [data$].[Type] = 'I' and [data$].[Cust #]='GL 888'

Now , when you use parameters , you should see that your strSQL after its assignment is identical to the above. Only then will your query work correctly.

Since the variable xtype contains the character I , the single quotes on either side have to be added. Similarly for the variable cmbcustno.Text

Narayan

I just sent you the file. did you see it?

I corrected the Distinct but the sum function still gives me a syntax error

rs.Open "SELECT Distinct [Inv #], [Cust #], [Date],[Due Date], sum[Amount] as InvBal FROM [data$] WHERE [data$].[Cust #]=" & "'" & cmbcustno.Text & "'", _
cnn, adOpenKeyset, adLockOptimistic

 

[Conrado Natac]

with this connection, is still possible to use the sql sum and group .
It wont even accept the sum for one field only.



cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name

'this works
'rs.Open "SELECT Distinct [Inv #], [Cust #], [Date],[Due Date], [Amount] as InvBal FROM [data$] WHERE [data$].[Type] = 'I' and" _
& " [data$].[Cust #]=" & "'" & cmbcustno.Text & "'", _
cnn, adOpenKeyset, adLockOptimistic



but when I put the group by, it does not work

'this gives the error
rs.Open "SELECT [Inv #], [Cust #], [Date],[Due Date], [Amount] as InvBal FROM [data$] WHERE [data$].[Type] = 'I' and" _
& " [data$].[Cust #]=" & "'" & cmbcustno.Text & "'" & "group by [Apply to]", _
cnn, adOpenKeyset, adLockOptimistic

 

[Conrado Natac]

Hey Naranya,

I posted this error as a new thread. I got an answer, but it is not working.

I was hoping you can look at it, when you are free.

Thanks a lot.

 

[Conrado Natac]

Hi Narayan,

I tried working on sub query but could not make it work. I was wondering, if you can point me to the right direction

This is the code with the error

strSQL = "SELECT C1.[Cust Name], c1.[Date], C1.[Due Date] from [data$] as c1, [data$] where c1.[Cust #] in SELECT [Apply to], Sum([Amount]) as InvBal FROM [data$] where [data$].[Cust #]= " & "'" & cmbcustno.Text & "'" & " group by [Apply to]"


rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

Thanks a lot for your help.

 

[NARAYANK991]

Hi Conrad ,

Can you check back later ?

Narayan

 

[Conrado Natac]

Thanks for looking at it.