UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error 3071...typed Incorrectly Or Too Complex, Access 2013    
 
   
vanzie
post May 12 2020, 03:06 AM
Post#1



Posts: 32
Joined: 29-January 19



Hi Everyone

I have a problem with my database where i use parameter search from one of my queries that is linked to a search form.

Untill recently, it worked well but somehow there are certain dates that i am searching for that gives error 3071. However, should i go to the query and enter the dates there, it shows all the date range records. If i search for the dates on my form, it gives me the error.

I have gone through all my records in the table to check for any irregular dates that might have been captured incorrectly but i found none. I have changed the format in the table and on the form and the report to Short Date which i have always used but it does not solve the problem.

The code that I use in my form gets flagged by this error:

CODE
DoCmd.OpenReport stDocName, acViewReport


Here is the full code:

CODE
Dim stDocName As String

    stDocName = "ByDate"

    'Check values are entered into Date From and Date To text boxes
    'if so run report or cancel request

    If Len(Me.txtStartDate & vbNullString) = 0 Or _
       Len(Me.txtEndDate & vbNullString) = 0 Then
        MsgBox "Please ensure that a report date range " & _
               "is entered into the form", _
               vbExclamation, "Required Data..."
        Exit Sub
    Else
    Me.Visible = False
        DoCmd.OpenReport stDocName, acViewReport
    End If


In my query is used this under the date criteria Between[Forms]![DateSearch]![txtStartDate] And [Forms]![DateSearch]![txtEndDate] the same as the controls that displays the date range in my report.

Any suggestions?


Go to the top of the page
 
Larry Larsen
post May 12 2020, 03:17 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,589
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
I seem to recognize the code segment from an example:

The difference I see between both are these instructions:
>= and <=

Example:
CODE
>=[Forms]![DateSearch]![txtStartDate] And <=[Forms]![DateSearch]![txtEndDate]


Have remove the "Between"

See if this makes a move..
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
vanzie
post May 12 2020, 03:22 AM
Post#3



Posts: 32
Joined: 29-January 19



Hi Larry

Thanks for your reply.

I changed it as you suggest but still no luck when using the form to search
Go to the top of the page
 
GroverParkGeorge
post May 12 2020, 06:48 AM
Post#4


UA Admin
Posts: 37,487
Joined: 20-June 02
From: Newcastle, WA


Please post the entire SQL from the query which is the recordsource for the report.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
BruceM
post May 12 2020, 07:57 AM
Post#5


UtterAccess VIP
Posts: 8,135
Joined: 24-May 10
From: Downeast Maine


QUOTE
there are certain dates that i am searching for that gives error 3071

Are you saying specific dates are causing the error? If so, an example may help.

Rather than checking for the length of the string you may do better using IsDate(Me.txtStartDate) and IsDate(Me.txtEndDate) to verify valid dates.
Go to the top of the page
 
vanzie
post May 12 2020, 10:01 AM
Post#6



Posts: 32
Joined: 29-January 19



Bruce, shall I use your suggestion for the query parameter or on the control fields of the report I intend to view?

It looks indeed like there are certain dates or maybe a field in a record that flags this error. But as I said, if I open the query itself and search the dates in the pop-up parameters, it shows the dates with no errors but it only happens on the form itself. If I search for a group of records say for example from 01 May until 07 May, it will show the error but if I search 01 May to 06 May it will show the records. Should I search from 07 May to 14 May, it will flag the error but search from 08 May....etc. You catch my drift. So somewhere in between my search criteria may be one or several date records that flags the error. I just can seem to figure out what it can be as I have used the date search criteria with all my other databases before and there's no problem at all.
Go to the top of the page
 
GroverParkGeorge
post May 12 2020, 10:28 AM
Post#7


UA Admin
Posts: 37,487
Joined: 20-June 02
From: Newcastle, WA


It would be really helpful to see both the full SQL of the query and some sample data. Can you provide that?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
BruceM
post May 12 2020, 12:19 PM
Post#8


UtterAccess VIP
Posts: 8,135
Joined: 24-May 10
From: Downeast Maine


I meant for my suggestion to supplement what George wrote. Without seeing the SQL it is mostly guesswork. It may turn out to be best to use the start and end dates as a Where Condition for OpenReport rather than as query parameters, but again, guesswork.
Go to the top of the page
 
vtd
post May 12 2020, 02:08 PM
Post#9


Retired Moderator
Posts: 19,777
Joined: 14-July 05
From: Sydney NSW Australia


If you use unbound Controls, Access may not recognize the values in the unbound Controls as Date values, especially if your default date format is not US format ("mm/dd/yyyy").

Your best bet is to explicitly declare the Parameters as of Date/Time data-type (instead of current undeclared/implicit Parameters) in the saved Query. In the Design View of the Query, select Design Menu and you should see the button for Parameters dialog where you can declare the parameters and their data-types.

Alternatively, use the function CDate() in your criterion, something like:

CODE
Between CDate([Forms]![DateSearch]![txtStartDate]) And CDate([Forms]![DateSearch]![txtEndDate])


Go to the top of the page
 
vanzie
post May 13 2020, 02:04 AM
Post#10



Posts: 32
Joined: 29-January 19



I'm attaching part of the database. If you search the form, it gives the error but searching the query doesn't. Hope this helps
Go to the top of the page
 
vanzie
post May 13 2020, 02:09 AM
Post#11



Posts: 32
Joined: 29-January 19



Here is the attachment
Attached File(s)
Attached File  Court.zip ( 331.59K )Number of downloads: 2
 
Go to the top of the page
 
Larry Larsen
post May 13 2020, 05:27 AM
Post#12


UA Editor + Utterly Certified
Posts: 24,589
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

Was a very puzzling issue and can only suggest and confirm that to get it to work was to rebuild the report...

Sorry I didn't actually rebuild your report but simply created a simple tab report..


Did assign a table format to your table field to "ShortDate"

Worth a try....

HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
vanzie
post May 13 2020, 07:34 AM
Post#13



Posts: 32
Joined: 29-January 19



I had the suspicion that the report is the thing that gives the problem but I will look into it and rebuild the report. I did change the data type of the date field to short date but didn't have any luck.

I will keep you posted if it worked or not.

Thanks Larry
Go to the top of the page
 
BruceM
post May 13 2020, 08:18 AM
Post#14


UtterAccess VIP
Posts: 8,135
Joined: 24-May 10
From: Downeast Maine


The problem seems to be that you are trying to do a sum of a text field on the report. It may have worked if the values could be interpreted as numbers, but they all seem to start with letters. I think if you just remove that control it will work.

Another point: there should be no need for CDate if you are in charge of the data. For a date you would do well do use DateSerial. For instance, the first day of any year:

DateSerial(Year(Date),1,1)

The last day:

DateSerial(Year(Date),12,31)

The last day of the current month (note that it is the 0 day of next month):

DateSerial(Year(Date),Month(Date) + 1,0)

Same for the date critieria in the query. Just use Forms!DateSearch!txtStartDate syntax. CDate isn't needed.

Note also that the report's sorting and grouping tool is most likely the best way to sort and group the data.

Another change I made was a simplified version of the validation code for the start and end dates on the form.

I have attached the database with some changes I have described, but maybe not all of them.
Attached File(s)
Attached File  Court_V2.zip ( 329.59K )Number of downloads: 1
 
Go to the top of the page
 
vanzie
post May 14 2020, 02:05 AM
Post#15



Posts: 32
Joined: 29-January 19



Thank you for taking the time to modify it, Bruce. I've imported the form and report to the main database and it works as it should
Go to the top of the page
 
BruceM
post May 14 2020, 06:41 AM
Post#16


UtterAccess VIP
Posts: 8,135
Joined: 24-May 10
From: Downeast Maine


Glad to hear it. In general, when having trouble with a form or report, save a copy, then delete half of the controls, or the controls in the header or footer, or something like that. If the problem persists, delete the other half, and so forth. If it comes down to a report with no controls or code, create a new report with the same name. If it still fails to open properly, most likely it isn't the report. With this report I deleted the calculated controls, and the problem went away. After that it was a simple matter to narrow it down to the Sum expression.

Good luck with the project.
Go to the top of the page
 
vanzie
post May 14 2020, 07:40 AM
Post#17



Posts: 32
Joined: 29-January 19



I will keep that in mind.

Thank you, Bruce and everyone for their insights
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th July 2020 - 01:14 PM