Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Runtime error 3075

Posted on 2011-09-27
16
Medium Priority
?
301 Views
Last Modified: 2012-05-12

 Hi,

I have the following code which is an adaptation of some other code I obtained. With it I am trying to check three different fields.

The error I'm getting the following error message: Syntax Error (missing operator in query expression.....



Private Sub uploading_data_to_ptf_Click()
Dim AppendDate As Date
Dim Source As String

    AppendDate = Forms![Uploading Menu]![contridate]
    Source = Forms![Uploading Menu]![wherefrom]
   
    If DCount("[Period] & [System]", "tbl_transactions", "Month([Period])= " & Month(AppendDate) & " AND " & "Year([Period]) =" & Year(AppendDate) & " AND " & "([System]) =" & Source()) > 0 Then
    MsgBox "Error", vbCritical
    Else
        DoCmd.RunMacro "Uploading Data to program"
    End If
   
End Sub

Grateful if someone could find the problem with it!  thanks
0
Comment
Question by:PipMic
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36710564
test this

    If DCount("[Period] & [System]", "tbl_transactions", "Month([Period])= " & Month(AppendDate) & " AND Year([Period]) =" & Year(AppendDate) & " AND [System] =" & Source()) > 0 Then


what is the Data Type of the field  [System] , Text or Number ?

if Text use this

   If DCount("[Period] & [System]", "tbl_transactions", "Month([Period])= " & Month(AppendDate) & " AND Year([Period]) =" & Year(AppendDate) & " AND [System] ='" & Source() & "'") > 0 Then

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36710767
Certain data types need delimiters in expressions -- Date values need # (or Chr(35)), and String values quotes, or Chr(39), which I prefer because it is more readable.  Here are some examples:
'Numeric filter
   lngID = Nz(Me![ID])
   If lngID <> 0 Then
      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
         & "[ID] = " & lngID & ";"
   End If

   'String filter
   strInventoryCode = Nz(Me![InventoryCode])
   If strInventoryCode <> "" Then
      strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
         & "[InventoryCode] = " & Chr$(39) & strInventoryCode & Chr$(39) & ";"
   End If

   'Date range filter from custom database properties
   dteFromDate = CDate(GetProperty("FromDate", ""))
   dteToDate = CDate(GetProperty("ToDate", ""))
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

   'Date range filter from controls
   If IsDate(Me![txtFromDate].Value) = True Then
      dteFromDate = CDate(Me![txtFromDate].Value)
   End If

   If IsDate(Me![txtToDate].Value) = True Then
      dteToDate = CDate(Me![txtToDate].Value)
   End If

   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & "[dteDateReceived] Between " & Chr(35) & dteFromDate _
      & Chr(35) & " And " & Chr(35) & dteToDate & Chr(35) & ";"

Open in new window

0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36711363
Try:

    Dim strCriteria as string
    strCriteria = "Format([Period], ""yyyymm"") = " & chr$(34) & Format(AppendDate, "yyyymm") & chr$(34) _
                     & " AND [System] =" & Source()
    If DCount("[Period] & [System]", "tbl_transactions", strCriteria) > 0 Then
        MsgBox "Error", vbCritical
    Else
        DoCmd.RunMacro "Uploading Data to program"
    End If
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PipMic
ID: 36712624
Hi capricorn 1 ,

None of the options worked!!

0
 

Author Comment

by:PipMic
ID: 36712627
Hi fyed,

Didnt work either!!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36712754
PipMic,
upload a copy of db
0
 

Author Comment

by:PipMic
ID: 36712817
Hi capricorn 1

Apologies the it didnt meant that I got the following message!

Compile error
Expected array

:)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36712850
that does not help... upload the db
0
 

Author Comment

by:PipMic
ID: 36712890
here goes
testv2003.mdb
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 total points
ID: 36713029


use this


    If DCount("[Period] & [System]", "tbl_transactions", "Month([Period])= " & Month(AppendDate) & " AND Year([Period]) =" & Year(AppendDate) & " AND [System] ='" & Source & "'") > 0 Then
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 800 total points
ID: 36713046
test this
testv2003Rev.mdb
0
 

Author Comment

by:PipMic
ID: 36713110
Hi,

It seems to work....will have to try tomorrow at work on the real one...many thanks

I will get back to you.asap.

But before I go can you at least explain the code?
0
 

Author Comment

by:PipMic
ID: 36813630
Hi capricorn,

It worked ..thanks.   :)
0
 

Author Comment

by:PipMic
ID: 36813639
I've requested that this question be closed as follows:

Accepted answer: 0 points for PipMic's comment http:/Q_27343671.html#36712890

for the following reason:

brilliant
0
 

Author Comment

by:PipMic
ID: 36813641
Gosh Ive made a mistake... I want to award Capricorn1 with all the points...


Please help
0
 

Author Closing Comment

by:PipMic
ID: 36813651
brilliant
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question