code for a delete query

  • Thread starter Thread starter matt donker via AccessMonster.com
  • Start date Start date
M

matt donker via AccessMonster.com

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
 
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
Click to expand...

Dim MonthNum as long
Dim strSQL as string
MonthNum= DatePart("m", Date) - 1
strSQL = "DELETE * " _
&" FROM [tblInventoryTaken] " _
&" WHERE DatePart('m', Date Taken) =" &MonthNum
 
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.
 
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.
Click to expand...

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
 
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


| 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
|
| --
|
 
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
 
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

| 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
|
|
| || 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
||
|| --
||
|
|
 
Back
Top