Allright i am a semi new access database developer and i am tearing my hair
out at this one.

I want to run some SQL from vb using and in the where statement make it
compart to a variable. This is what i have (Well there is a ton more code
but this is what is significant)

Dim MonthNum$
Dim strSQL
MonthNum$ = DatePart("m", Date) - 1
strSQL = "DELETE * FROM [tblInventoryTaken] WHERE DatePart(m, Date Taken) =
MonthNum$"

Now i have changed so much around to try to get this thing to work and
regardless of what i do it always comes up with an error. One problem i am
having is that the date part statement you cannot use double quotes around
the m or you get and expected end of line error. Anyway if any of you can
help out i would sure appreciate it thanks.

Matt

--
Message posted via http://www.accessmonster.com

On Fri, 18 Feb 2005 14:47:34 GMT, "matt donker via AccessMonster.com"
<(E-Mail Removed)> wrote:

>Allright i am a semi new access database developer and i am tearing my hair
>out at this one.
>
>I want to run some SQL from vb using and in the where statement make it
>compart to a variable. This is what i have (Well there is a ton more code
>but this is what is significant)
>
>Dim MonthNum$
>Dim strSQL
>MonthNum$ = DatePart("m", Date) - 1
>strSQL = "DELETE * FROM [tblInventoryTaken] WHERE DatePart(m, Date Taken) =
>MonthNum$"
>
>Now i have changed so much around to try to get this thing to work and
>regardless of what i do it always comes up with an error. One problem i am
>having is that the date part statement you cannot use double quotes around
>the m or you get and expected end of line error. Anyway if any of you can
>help out i would sure appreciate it thanks.
>
>Matt

Dim MonthNum as long
Dim strSQL as string
MonthNum= DatePart("m", Date) - 1
strSQL = "DELETE * " _
&" FROM [tblInventoryTaken] " _
&" WHERE DatePart('m', Date Taken) =" &MonthNum
---
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

That looks right to me the only problem is when it runs i get a syntax
error. Missing operator in the following part of code. The error number
is 3075 if that helps.

& " WHERE DatePart('m', Date Taken) =" & MonthNum

I tell you this is driving me insane.

--
Message posted via http://www.accessmonster.com

On Fri, 18 Feb 2005 16:40:15 GMT, "matt donker via AccessMonster.com"
<(E-Mail Removed)> wrote:

>That looks right to me the only problem is when it runs i get a syntax
>error. Missing operator in the following part of code. The error number
>is 3075 if that helps.
>
> & " WHERE DatePart('m', Date Taken) =" & MonthNum
>
>I tell you this is driving me insane.

this is right, this should you drive insane

everybody who uses blanks in a field name is in this stage :-)

WHERE DatePart('m', [Date Taken]) =" & MonthNum

you have to use [] if you don't use "normal" letter and numers
---
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

Dim MonthNum as Long
Dim DTMonth as Long
Dim strSQL as String
DTMonth = DatePart("m", Date Taken)
MonthNum = DatePart("m", Date) - 1
strSQL = "Delete * from tblInventoryTaken" _
& "where DTMonth = " & MonthNum
DoCmd.RunSQL strSQL


"matt donker via AccessMonster.com" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
| Allright i am a semi new access database developer and i am tearing my hair
| out at this one.
|
| I want to run some SQL from vb using and in the where statement make it
| compart to a variable. This is what i have (Well there is a ton more code
| but this is what is significant)
|
| Dim MonthNum$
| Dim strSQL
| MonthNum$ = DatePart("m", Date) - 1
| strSQL = "DELETE * FROM [tblInventoryTaken] WHERE DatePart(m, Date Taken) =
| MonthNum$"
|
| Now i have changed so much around to try to get this thing to work and
| regardless of what i do it always comes up with an error. One problem i am
| having is that the date part statement you cannot use double quotes around
| the m or you get and expected end of line error. Anyway if any of you can
| help out i would sure appreciate it thanks.
|
| Matt
|
| --
| Message posted via http://www.accessmonster.com

Now i am just want to say i did try using the square brackets and it didn't
work. Now get this all i had to do after placing the squarebrackets in was
exit out of the whole database and then reopen it. Then it worked. I
swear access is gunna make me become crazy. Anyways thanks for all the help
dude. U saved my life

--
Message posted via http://www.accessmonster.com

Sorry, I don't use spaces in my fields and missing putting [ ] around Date
Taken.

Dim MonthNum as Long
Dim DTMonth as Long
Dim strSQL as String
DTMonth = DatePart("m", [Date Taken])
MonthNum = DatePart("m", Date) - 1
strSQL = "Delete * from tblInventoryTaken" _
& "where DTMonth = " & MonthNum
DoCmd.RunSQL strSQL

"DebbieG" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
| Dim MonthNum as Long
| Dim DTMonth as Long
| Dim strSQL as String
| DTMonth = DatePart("m", Date Taken)
| MonthNum = DatePart("m", Date) - 1
| strSQL = "Delete * from tblInventoryTaken" _
| & "where DTMonth = " & MonthNum
| DoCmd.RunSQL strSQL
|
|
| "matt donker via AccessMonster.com" <(E-Mail Removed)> wrote in message
| news:(E-Mail Removed)...
|| Allright i am a semi new access database developer and i am tearing my hair
|| out at this one.
||
|| I want to run some SQL from vb using and in the where statement make it
|| compart to a variable. This is what i have (Well there is a ton more code
|| but this is what is significant)
||
|| Dim MonthNum$
|| Dim strSQL
|| MonthNum$ = DatePart("m", Date) - 1
|| strSQL = "DELETE * FROM [tblInventoryTaken] WHERE DatePart(m, Date Taken) =
|| MonthNum$"
||
|| Now i have changed so much around to try to get this thing to work and
|| regardless of what i do it always comes up with an error. One problem i am
|| having is that the date part statement you cannot use double quotes around
|| the m or you get and expected end of line error. Anyway if any of you can
|| help out i would sure appreciate it thanks.
||
|| Matt
||
|| --
|| Message posted via http://www.accessmonster.com
|
|