SQL string syntax problem

[radek225]

I have problem to implement "short date" in format to sqlstring
(syntax)
what's wrong with my sqlstring? Ms Access shows me that is something wrong with "short date" part

    strsqLy = "select datarozpoczecia, datazakonczenia from " & _
    "tblPRocesyZleceniaSzczegoly inner join (tblZmianySzczegoly inner join tblZmiany on tblZmianySzczegoly.id_zmiany = tblZmiany.id_zmiany) " & _
    "on tblProcesyZleceniaSzczegoly.pracownik = tblZmianySzczegoly.pracownik " & _
    "where format(#datazmiany#, "short date")=#" & Format(#Now()#, "short date") & "# and nz(datarozpoczecia, 0)>" & 0 & " and nz(datazakonczenia, 0)=" & 0

 

[pbaldy]

You don't want the # inside the Format() function for starters.

 

[radek225]

You don't want the # inside the Format() function for starters.

without # still have a syntax error

 

[pbaldy]

The double quotes in the first need to be singles; the doubles close out the string.

 

[radek225]

The double quotes in the first need to be singles; the doubles close out the string.

Could you show me this on my strsql link? Then i will know...

 

[radek225]

Functions like Format or Nz have to be concatenated into the string. When you are forming this type of SQL string use the intellisense to see if you are on the right track. For example when you typed:

"where format(

the intellisense should have kicked in. It didn't because you were still in a string. If you type

"where " & format(

you will see what I mean. I suggest reading

http://www.techonthenet.com/access/functions/string/concat.php

too.

still I do not know how my link should look like:/

 

[pbaldy]

Could you show me this on my strsql link? Then i will know...

format(datazmiany, 'short date')

 

[radek225]

format(datazmiany, 'short date')

doesn't work. Could you show me, how all link should looks like?

 

[radek225]

still can't write this string properly:/. Guys any ideas?

 

[Cronk]

I'll be generous
strsqLy = "select datarozpoczecia, datazakonczenia from " & _ "tblPRocesyZleceniaSzczegoly inner join (tblZmianySzczegoly inner join tblZmiany on tblZmianySzczegoly.id_zmiany = tblZmiany.id_zmiany) " & _ "on tblProcesyZleceniaSzczegoly.pracownik = tblZmianySzczegoly.pracownik " & _ "where format(datazmiany, 'short date')=#" & Format(Now(), "short date") & "# and nz(datarozpoczecia, 0)>0 and nz(datazakonczenia, 0)=0"

 

[radek225]

I'll be generous
strsqLy = "select datarozpoczecia, datazakonczenia from " & _ "tblPRocesyZleceniaSzczegoly inner join (tblZmianySzczegoly inner join tblZmiany on tblZmianySzczegoly.id_zmiany = tblZmiany.id_zmiany) " & _ "on tblProcesyZleceniaSzczegoly.pracownik = tblZmianySzczegoly.pracownik " & _ "where format(datazmiany, 'short date')=#" & Format(Now(), "short date") & "# and nz(datarozpoczecia, 0)>0 and nz(datazakonczenia, 0)=0"

Thanks for your help. Now it works