A variable in ADODC 3

[draugen]

First of all, after a year or so, I am finaly back to programming/updating my program, and to everyone that have answered my questions is the past and gotten "silens" back.
I apologize.
I need to filter 'Names' in an SQL statement. I have a datagrid that is being populated with data from an access file. The RecordSource of my Adodc is:

SELECT Forfall, ID, Ansvarlig, Eiendom, Bygg, Anlegg, [Ordre Beskrivelse]
FROM [Vedlikeholds Rutiner]
WHERE ([Kvitter ut] <> 0)
AND (Ansvarlig <> ".")
ORDER BY Bygg DESC, Forfall ASC

Could I use a variable, in the RecordSource of my Adodc, to be determined at run time by the user?

Thanks ahead of time.

 

[draugen]

Forgot to say where I was thinking about using the variable. The variable sould go in the statment:

...
AND (Ansvarlig = a)
...

Is this possible using it in the RecordSource of my Adodc, this way?

Thanks ahead of time.

 

[SBerthold]

For text/memo fields:

AND (Ansvarlig = '" & aVariable & "')"

for number fields
AND (Ansvarlig = " & aVariable & ")

 

[BobRodes]

<Is this possible using it in the RecordSource of my Adodc, this way?

Yes it is. You can set the Recordsource in code to a new value and then use the Refresh method to get the new data:

With Adodc1
   .RecordSource = "SELECT Forfall, ID, " & _
   "Ansvarlig, Eiendom, Bygg, Anlegg, [Ordre Beskrivelse] " & _
   "FROM [Vedlikeholds Rutiner] " & _
   "WHERE ([Kvitter ut] <> 0) " & _
   "AND (Ansvarlig = [COLOR=red]'" & a & "'[/color])" & _
   "ORDER BY Bygg DESC, Forfall ASC"
   .Refresh
End With

Based on my understanding of the word "Ansvarlig", I'm assuming that your variable is a string rather than a number.

Bob

 

[draugen]

Thank you SBerthold and BobRodes.

And Bob your good in languages! Yes, "Ansvarlig"
is a string. "Ansvarlig" is norwegian, meaning: the one(s) "reponsible" for someting.

I'm going to try it out and let you know.

Christian

 

[BobRodes]

Thanks for the compliment. Google was a most useful tool in determining the meaning of the word.

Just for fun:

A simple literal translation:

SELECT Decay, ID, " & _
   "Liable, Premises, Barley, Gift, [Order description] " & _
   "FROM [Maintenance Procedure] " & _
   "WHERE ([twittering out] <> 0) " & _
   "AND (Responsible = '" & a & "')" & _
   "ORDER BY Barley DESC, Decay ASC"

The word "bygg" is a particularly fascinating one. Anyway, here's a shot at a better translation:

SELECT Failure, ID, ResponsibleParty, Area, Building, Facility, [Order Description]
FROM [Maintenance Procedure]
WHERE ([Received] <> 0) 
AND (ResponsibleParty <> ".")
ORDER BY Building DESC, Failure ASC

 

[draugen]

Sorry about the time that has gone by since last question.
but needed to work on some thing else. But now I am back to my program.
I chose a case stucture instead of an if-then-else stucucture.
This is my proposed case structure

Select Case gintFlagFilter

  Case 0

    With Adodc1
      .Recordsource = “SELECT Forfall, ID, “ & _
      “Ansvarlig, Eiendom, Bygg, Anlegg, [Ordre Beskrivelse] “ & _
      “FROM [Vedlikeholds Rutiner] “ & _
      “WHERE  ([Kvitter ut] = 0) “ & _
      “AND (Forfall <= date()) “ & _
      “AND (Ansvarlig <> ".") “ & _
      “ORDER BY Forfall ASC”
      .Refresh
    End With			

  Case 1

    With Adodc1
      .Recordsource = “SELECT Forfall, ID, “ & _
      “Ansvarlig, Eiendom, Bygg, Anlegg, [Ordre Beskrivelse] “ & _
      “FROM [Vedlikeholds Rutiner] “ & _
      “WHERE  ([Kvitter ut] = 0) “ & _
      “AND (Forfall <= date()) “ & _
      “AND (Ansvalig = ‘” & gstrFilteredName & “’) ” & _
      “AND (Ansvarlig <> ".") “ & _
      “ORDER BY Forfall ASC”
      .Refresh
    End With

End Select

This I coded into the program, but it will not except the period in

      “AND (Ansvarlig <> ".") “ & _

The Error message I get is

Compile Error

Expected: End of statement

If I take this statement away, then the program seem to except the whole SQL statement (no red writing).

I need to exclude where anvarlig is ".", though.

Can someone tell me what I am doing wrong?

hanks ahead of time.

 

[Golom]

Use single quotes or double up the double quotes

"AND (Ansvarlig <> [red]'.'[/red]) " & _

or 

"AND (Ansvarlig <> [red]"".""[/red]) " & _

 

[draugen]

Thank you all! The filtering of the names works splendidly, now. SQL is a very powerful tool too have around, but the syntax has to be perfect! Again thanks for your help.
Christian