Error massage invalid Brackeing of name in query (1 Viewer)

[Tom d]

Getting this error massage in a criteria field in a query
Between[[Forms]![frmSelected_Statistics]![(CDate lst_From_Date.Column(1))] And [Forms]![frmSelected_Statistics]![(CDate lst_To_Date.Column(1))]

 

[CJ_London]

space missing after Between and you can't refer to a control property such as a column. If column(1) is your bound column, you do not need to specify it. You also have that control with brackets

[(CDate lst_To_Date.Column(1))]

should just be

[CDate lst_To_Date]

 

[MajP]

Pretty far off any reasonable syntax. My guess.

If you can bind the listboxes to column 1 then you can get rid of the column reference.

Between cdate([Forms]![frmSelected_Statistics]![lst_From_Date]) And cdate([Forms]![frmSelected_Statistics]![lst_To_Date])

 

[Tom d]

Pretty far off any reasonable syntax. My guess.

Between cdate([Forms]![frmSelected_Statistics]![lst_From_Date].Column(1)) And cdate([Forms]![frmSelected_Statistics]![lst_To_Date].Column(1))

gives error massage The expression you entered has an invalid .(Dot) or ! operator on invalid parentheses

 

[CJ_London]

as already explained you cannot reference the column. But agree with Maj's point about using cdate

 

[Tom d]

Pretty far off any reasonable syntax. My guess.

If you can bind the listboxes to column 1 then you can get rid of the column reference.

Between cdate([Forms]![frmSelected_Statistics]![lst_From_Date]) And cdate([Forms]![frmSelected_Statistics]![lst_To_Date])

gives a box to enter the from date and the to date

 

[CJ_London]

four possibilities:
1. frmSelected_Statistics is not the correct spelling
2. frmSelected_Statistics is not open
3. your query is a crosstab - in which case you need to specify the parameters - see the parameters button on the ribbon. If this is the case, probably you don't need the cdate function since in the parameters you specify the datatype.
4. any combination of the above

 

[Tom d]

four possibilities:
1. frmSelected_Statistics is not the correct spelling
2. frmSelected_Statistics is not open
3. your query is a crosstab - in which case you need to specify the parameters - see the parameters button on the ribbon. If this is the case, probably you don't need the cdate function since in the parameters you specify the datatype.
4. any combination of the above

Form is spelled correctly.
form will be opened when running query.

 

[Tom d]

gives a box to enter the from date and the to date

Sorry, Column 1 contains The Id field

 

[The_Doc_Man]

As to the original error mentioned in the first post, IF that example you showed us was a cut/paste then you had a double left bracket ([[) near the beginning of the clause. AND you needed a space after the Between as noted earlier by CJL.

When you get a box asking you to enter specific field names, that is the Access not-so-subtle way of telling you "I can't see anything with that name in the place where you said it was." Could be the spelling of the thing, the spelling of the location, maybe it cannot be seen in that context, or whatever is the bound column is incorrect. Can't tell from here about the latter option because not enough evidence.

If the controls in question are combo boxes and the bound column isn't the date field, I would say that your only two remaining choices are:
(a) Make the two values become parameters in a parameter query and then define those parameters before running the query, OR
(b) Dynamically build the query as a string so that it appears to have the correct values as constants.

 

[Pat Hartman]

Actually,

[(CDate lst_To_Date.Column(1))]

Is

CDate lst_To_Date.Column(1)

If you need the square brackets, then:

[CDate lst_To_Date].Column(1) --- they encase the name of the control, NOT it's properties.

Remember, the Column property is a zero based array so
CDate lst_To_Date.Column(0) = CDate_lst_to_Date and .Column(1) is the SECOND column of the RowSource.

 

[CJ_London]

you've made the same mistake I did CDate is a function. Majp got it right

 

[Pat Hartman]

The parentheses were so messed up, it got me too

 

[spaLOGICng]

Getting this error massage in a criteria field in a query
Between[[Forms]![frmSelected_Statistics]![(CDate lst_From_Date.Column(1))] And [Forms]![frmSelected_Statistics]![(CDate lst_To_Date.Column(1))]

First, you cannot refer to the Column Property in a Query Criteria Expression.

If you have a Combo Box with a Data and the Bound Column is 1 and is an ID, change the Bound Column to "2", or reposition the Columns in your Combo Box so that the Date is the First Column.

Your expression should look like this:
Between [Forms]![frmSelected_Statistics]![1st_From_Date] And [Forms]![frmSelected_Statistics]![1st_To_Date]

 

[June7]

If you need a value from combobox or listbox column other than the first one, have a textbox with expression referencing that column by its index: =[1st_ToDate].Column(1). Then query can reference that textbox to pull value.

Why would a combobox or listbox for listing dates have multiple columns?

I don't use dynamic parameterized queries. I use VBA to build filter criteria and apply to form or report.

 

[spaLOGICng]

You need to stop referring to the Column Property.

gives error massage The expression you entered has an invalid .(Dot) or ! operator on invalid pa

 

[onur_can]

another way is to put the column you refer to in the first row of your query in the row source of the combo box. this way, you don't apply for a column, you only refer to the dropdown box itself.

 

[Pat Hartman]

If you used the normal method of defining the key as the FIRST column as I suggested, you wouldn't have this problem because you would not need to include the column property since .Column(0) = .value and since .value is the default value, you just omit it in the query so just using lst_To_Date.Column would be correct.

Otherwise, as the others have already suggested, you MUST copy lst_To_Date.Column(1) to a control so you can refer to that control name without any property value to qualify it.