Runtime queries

[Neil_W]

Hi guys,

I am working in Access 2003 andtrying to populate a form from an underlying query written on the fly. My code is as follows:

Private Sub cmdDefra_Form_Click()
Dim Cat As New ADOX.Catalog
Dim cmd As ADODB.Command
Dim sqlText As String


Set Cat.ActiveConnection = CurrentProject.Connection
Set cmd = New ADODB.Command

date1 = Forms!frmDefra_Select.txtDate1
date1 = Format(date1, "mm/dd/yy")
date2 = Forms!frmDefra_Select.txtDate2
date2 = Format(date2, "mm/dd/yy")
sqlText = "select * From qryWSF_Defra where date_of_purchase between " & "#" & date1 & "#" & _
" and " & "#" & date2 & "#"


cmd.CommandText = sqlText
Cat.Views.Append "qryDefra_Response", cmd


Set cmd = Nothing
Set Cat = Nothing
End Sub

This gives a MS error message on the line "Cat.Views.Append "qryDefra_Response", cmd" that throws me out of Access.

Any ideas?

Neil_W

[AndreRet]

Can you please specify the error message.

[Neil_W]

Regrettably not. Access crashes completely.

[AndreRet]

I am not at all clueded up on Access. From what I could gateher is the following:

You are selecting data from table qryWSF_Defra, but you ask for a view/append of table qryDefra_Response.
Also look at your date formats. It looks fine from here, but was your data saved in the same format?

As I mentioned, I'm at a loss here. This is all guessing. Have you tried another forum, maybe databases?

I've managed to find the following link. See, maybe it helps -

http://msdn.microsoft.com/en-us/library/ms677590(VS.85).aspx OR
http://www.dbforums.com/visual-basic/1646867-help-vba.html OR
http://bytes.com/topic/access/answers/868453-access-dynamic-sql

The last seems to handle your quiry more in detail. What I have picked up is that you maybe have to refresh as well -

Dim cat As New ADOX.Catalog 
Dim cmd As New ADODB.Command 
cat.ActiveConnection = CurrentProject.Connection 
  
On Error Resume Next 
cat.Views.Delete "qryFilter" 'deletes the query if it already exists, ignores errors if it doesn't 
On Error GoTo 0 
  
cmd.CommandText = sql 'this should be your sql SELECT string 
  
cat.Views.Append "qryFilter", cmd 'makes a new one 
cat.Views.Refresh 'this is meant to make it so you can view the new query in the query tab, it doesn't always work. however if you then go to manually create a new query, you can see 'qryFilter' has been created as its in the drop down 
  
Set cat = Nothing 
Set cmd = Nothing

Good luck on finding the absolute solution. Please keep me posted, I'dd like to see where the problem came in.
Thanks

[omoridi]

access don't have between chode >= and <= for that

dim cnn as new connection
dim rs as new recordset

cnn.open "asdasdasdasdasd"
rs.open "select * from ....",cnn,....

'or

set rs=cnn.excute("select * from ......")

[vb5prgrmr]

yes access does as it is part of the ansi standard....

Between...And Operator
Determines whether the value of an expression falls within a specified range of values. You can use this operator within SQL statements .

Syntax
expr [Not] Between value1 And value2

The Between...And operator syntax has these parts:

Part Description
expr Expression identifying the field that contains the data you want to evaluate.
value1, value2 Expressions against which you want to evaluate expr.

Remarks
If the value of expr is between value1 and value2 (inclusive), the Between...And operator returns True; otherwise, it returns False. You can include the Not logical operator to evaluate the opposite condition (that is, whether expr lies outside the range defined by value1 and value2).

You might use Between...And to determine whether the value of a field falls within a specified numeric range. The following example determines whether an order was shipped to a location within a range of postal codes. If the postal code is between 98101 and 98199, the IIf function returns “Local”. Otherwise, it returns “Nonlocal”.

SELECT IIf(PostalCode Between 98101 And 98199, “Local”, “Nonlocal”)

FROM Publishers

If expr, value1, or value2 is Null , Between...And returns a Null value.

Because wildcard characters , such as *, are treated as literals, you cannot use them with the Between...And operator. For example, you cannot use 980* and 989* to find all postal codes that start with 980 to 989. Instead, you have two alternatives for accomplishing this. You can add an expression to the query that takes the left three characters of the text field and use Between...And on those characters. Or you can pad the high and low values with extra characters— in this case, 98000 to 98999, or 98000 to 98999 – 9999 if using extended postal codes. (You must omit the – 0000 from the low values because otherwise 98000 is dropped if some postal codes have extended sections and others do not.)

See Also
IN Clause SQL Expressions
WHERE Clause

See Also
Buy Microsoft Office 2007
Free trial of the 2007 Microsoft Office system

Was this information helpful?

Please tell Microsoft how this information was helpful (optional): Please tell Microsoft how we can make this information more helpful (optional):
Please tell Microsoft what you were trying to do (optional):

Your feedback is being submitted.

Thank you for submitting your feedback to Microsoft.
Change my feedback
Although Microsoft cannot respond to every comment, we do use your feedback to improve the information we provide.

Do you need support, or do you have a suggestion about a product? Contact us.

Contact Us
Privacy Statement