Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community Forums > Databases > Microsoft Access Development
Reload this Page

Going to kill myself...

Discuss Going to kill myself... in the Microsoft Access Development forum on Dev Articles.
Going to kill myself... Microsoft Access Development forum to discuss problems and solutions with this popular DBMS. Use Access to build and modify database tables, or full-featured applications.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old February 25th, 2005, 06:16 AM
xpetex xpetex is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Feb 2005
Posts: 56 xpetex User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 19 h 50 m 7 sec
Reputation Power: 4
Going to kill myself...
I cant understand why this isnt working, I get a runtime error 2001, 'you cancelled the previous operation.

This works fine:

Code:
Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String, strFROM As String, strWHERE As String, strCUSTOMER As String, strORDERBY As String, strDAY As String
strSELECT = "*"
strFROM = "[mainstatic]"
strWHERE = ""
If Not IsNull(Shopnumbertxt) Then strWHERE = strWHERE & " And [mainstatic]![Job number] LIKE [Forms]![view job]![Shopnumbertxt]"
If Not IsNull(jobstatusfilter) Then strWHERE = strWHERE & " And [mainstatic]![Job status] = [Forms]![view job]![jobstatusfilter]"
If Not IsNull(stafffilter) Then strWHERE = strWHERE & " And [mainstatic]![staff 1] = [Forms]![view job]![stafffilter]"
If Not IsNull(priorityfilter) Then strWHERE = strWHERE & " And [mainstatic]![priority] = [Forms]![view job]![priorityfilter]"
If Not IsNull(datestarttxt) Then strWHERE = strWHERE & " And [mainstatic]![Install date] >= [Forms]![view job]![datestarttxt]"
If Not IsNull(enddatetxt) Then strWHERE = strWHERE & " And [mainstatic]![Install date] <= [Forms]![view job]![enddatetxt]"
If Not IsNull(jobtypefilter) Then strWHERE = strWHERE & " And [mainstatic]![Job type] = [Forms]![view job]![jobtypefilter]"
If urgent = True Then strWHERE = strWHERE & " And [mainstatic]![Install Date] < Date() And [mainstatic]![Job status] <> 'Completed'"
If nonurgent = True Then strWHERE = strWHERE & " And [mainstatic]![Install Date] > Date() And [mainstatic]![Job status] <> 'Completed'"
If email = True Then strWHERE = strWHERE & " And [mainstatic]![emailsent] = False "
If notemail = True Then strWHERE = strWHERE & " And [mainstatic]![emailsent] = True "
If invoiced = True Then strWHERE = strWHERE & " And [mainstatic]![invoiced] = True "
If notinvoiced = True Then strWHERE = strWHERE & " And [mainstatic]![invoiced] = False And ((([mainstatic]![job status]) <> 'requested') And (([mainstatic]![job status]) <> 'Agreed'))"

strCUSTOMER = ""
If aramiskacheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "aramiska" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If bespokecheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "bespoke" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If btcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "bt" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If coralcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "coral" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If kingstoncheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "Kingston" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If mducheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "mdu" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If pipexcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "pipex" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If patientlinecheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "patientline" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If residentialcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "residential" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If sportstvcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "sports tv" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If officecheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "office" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If strCUSTOMER <> "" Then
strCUSTOMER = Left$(strCUSTOMER, Len(strCUSTOMER) - 30)
strWHERE = strWHERE & " And " & "((" & "[mainstatic]![contract]" & ")" & "=" & strCUSTOMER & ")"
Else
strWHERE = strWHERE
End If

strSQL = "SELECT" & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
strSQL = strSQL & "ORDER BY" & "[mainstatic]![Install date]" & "ASC"
strSQL = strSQL
BuildSQLString = True
End Function


but If I tru to add a date filter in it doesnt work:
Code:
Function BuildSQLString(strSQL As String) As Boolean
Dim strSELECT As String, strFROM As String, strWHERE As String, strCUSTOMER As String, strORDERBY As String, strDAY As String
strSELECT = "*"
strFROM = "[mainstatic]"
strWHERE = ""
If Not IsNull(Shopnumbertxt) Then strWHERE = strWHERE & " And [mainstatic]![Job number] LIKE [Forms]![view job]![Shopnumbertxt]"
If Not IsNull(jobstatusfilter) Then strWHERE = strWHERE & " And [mainstatic]![Job status] = [Forms]![view job]![jobstatusfilter]"
If Not IsNull(stafffilter) Then strWHERE = strWHERE & " And [mainstatic]![staff 1] = [Forms]![view job]![stafffilter]"
If Not IsNull(priorityfilter) Then strWHERE = strWHERE & " And [mainstatic]![priority] = [Forms]![view job]![priorityfilter]"
If Not IsNull(datestarttxt) Then strWHERE = strWHERE & " And [mainstatic]![Install date] >= [Forms]![view job]![datestarttxt]"
If Not IsNull(enddatetxt) Then strWHERE = strWHERE & " And [mainstatic]![Install date] <= [Forms]![view job]![enddatetxt]"
If Not IsNull(jobtypefilter) Then strWHERE = strWHERE & " And [mainstatic]![Job type] = [Forms]![view job]![jobtypefilter]"
If urgent = True Then strWHERE = strWHERE & " And [mainstatic]![Install Date] < Date() And [mainstatic]![Job status] <> 'Completed'"
If nonurgent = True Then strWHERE = strWHERE & " And [mainstatic]![Install Date] > Date() And [mainstatic]![Job status] <> 'Completed'"
If email = True Then strWHERE = strWHERE & " And [mainstatic]![emailsent] = False "
If notemail = True Then strWHERE = strWHERE & " And [mainstatic]![emailsent] = True "
If invoiced = True Then strWHERE = strWHERE & " And [mainstatic]![invoiced] = True "
If notinvoiced = True Then strWHERE = strWHERE & " And [mainstatic]![invoiced] = False And ((([mainstatic]![job status]) <> 'requested') And (([mainstatic]![job status]) <> 'Agreed'))"

strDAY = ""
If daytoday = True Then strDAY = strDAY & Chr(34) & "Date()" & Chr(34) & " Or ([mainstatic]![Install date]" & ") ="
If Daytoday1 = True Then strDAY = strDAY & Chr(34) & "Date()+1" & Chr(34) & " Or ([mainstatic]![Install date]" & ") ="
If Daytoday2 = True Then strDAY = strDAY & Chr(34) & "Date()+2" & Chr(34) & " Or ([mainstatic]![Install date]" & ") ="
If Daytoday3 = True Then strDAY = strDAY & Chr(34) & "Date()+3" & Chr(34) & " Or ([mainstatic]![Install date]" & ") ="
If Daytoday4 = True Then strDAY = strDAY & Chr(34) & "Date()+4" & Chr(34) & " Or ([mainstatic]![Install date]" & ") ="
If Daytoday5 = True Then strDAY = strDAY & Chr(34) & "Date()+5" & Chr(34) & " Or ([mainstatic]![Install date]" & ") ="
If Daytoday6 = True Then strDAY = strDAY & Chr(34) & "Date()+6" & Chr(34) & " Or ([mainstatic]![Install date]" & ") ="
If strDAY <> "" Then
strDAY = Left$(strDAY, Len(strDAY) - 35)
strWHERE = strWHERE & " And " & "((" & "[mainstatic]![Install date]" & ")" & "=" & strDAY & ")"
Else
strWHERE = strWHERE
End If

strCUSTOMER = ""
If aramiskacheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "aramiska" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If bespokecheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "bespoke" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If btcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "bt" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If coralcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "coral" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If kingstoncheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "Kingston" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If mducheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "mdu" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If pipexcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "pipex" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If patientlinecheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "patientline" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If residentialcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "residential" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If sportstvcheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "sports tv" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If officecheck = True Then strCUSTOMER = strCUSTOMER & Chr(34) & "office" & Chr(34) & " Or ([mainstatic]![contract]" & ") ="
If strCUSTOMER <> "" Then
strCUSTOMER = Left$(strCUSTOMER, Len(strCUSTOMER) - 30)
strWHERE = strWHERE & " And " & "((" & "[mainstatic]![contract]" & ")" & "=" & strCUSTOMER & ")"
Else
strWHERE = strWHERE
End If

strSQL = "SELECT" & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)
strSQL = strSQL & "ORDER BY" & "[mainstatic]![Install date]" & "ASC"
strSQL = strSQL
BuildSQLString = True
End Function


any ideas????
Reply With Quote
  #2  
Old February 25th, 2005, 06:26 AM
xpetex xpetex is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Feb 2005
Posts: 56 xpetex User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 19 h 50 m 7 sec
Reputation Power: 4
sorted it, took out the Chr(34) 's
Reply With Quote
Reply

Viewing: Dev Articles Community Forums > Databases > Microsoft Access Development > Going to kill myself...

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway. 
Stay green...Green IT