Replies

Hope a zip file is OK?

Thanks for the replies.

OK, i’ve taken out the spaces.

I then did the immediate window bit, thanks that’s going to be a useful tip

Pasted the code into the Query SQL window, nothing is returned (I was expecting this but at least I now KNOW). I then had a quick ‘play’ with the Query in Design view and it returned the correct results. On viewing it in SQL it now looks like this: –

SELECT *
FROM PrimaryDataTbl
WHERE (((PrimaryDataTbl.DateReceived) Between #1/1/2000# And #9/1/2004#) AND ((PrimaryDataTbl.VendorNumber)=17999));

However, if I try that in my VBA it doesn’t like it at all, not that I really expected it to, but desperation drives you to wierd things….

I then tried defining my fields in the query (PrimaryDataTbl.FieldName) to see if that helped, still no luck.

So, is there something in my SQL statement that is causing the problem? I could create a Query and use QueryDefs, but that seems to be overkill for something this simple.

I’ll look into stripping the database out to get it to < 100k.

Thanks again.

Ian

Thanks for the replies.

OK, i’ve taken out the spaces.

I then did the immediate window bit, thanks that’s going to be a useful tip

Pasted the code into the Query SQL window, nothing is returned (I was expecting this but at least I now KNOW). I then had a quick ‘play’ with the Query in Design view and it returned the correct results. On viewing it in SQL it now looks like this: –

SELECT *
FROM PrimaryDataTbl
WHERE (((PrimaryDataTbl.DateReceived) Between #1/1/2000# And #9/1/2004#) AND ((PrimaryDataTbl.VendorNumber)=17999));

However, if I try that in my VBA it doesn’t like it at all, not that I really expected it to, but desperation drives you to wierd things….

I then tried defining my fields in the query (PrimaryDataTbl.FieldName) to see if that helped, still no luck.

So, is there something in my SQL statement that is causing the problem? I could create a Query and use QueryDefs, but that seems to be overkill for something this simple.

I’ll look into stripping the database out to get it to < 100k.

Thanks again.

Ian

Francois

As yet I don’t what’s different between your SQL string and mine, but whatever it is it works another learning opportunity provided by the good folks of Woody’s lounge

Thanks

Ian

Francois

As yet I don’t what’s different between your SQL string and mine, but whatever it is it works another learning opportunity provided by the good folks of Woody’s lounge

Thanks

Ian

Arrays look like they might be my answer, now how would I refer to the data in the array? With a recordset I can use RST!ItemName and loop through it using the MoveNext instruction. Looking at the Using Arrays help section there are not hints on how to do the same, getting data in appears to be easy enough even for me to manage

I’ll keep reading, but if there’s any easy way, send it my way please

Thanks

Ian

Arrays look like they might be my answer, now how would I refer to the data in the array? With a recordset I can use RST!ItemName and loop through it using the MoveNext instruction. Looking at the Using Arrays help section there are not hints on how to do the same, getting data in appears to be easy enough even for me to manage

I’ll keep reading, but if there’s any easy way, send it my way please

Thanks

Ian

Well, the demo looks nice, however, it’s taken me a while to gain any level of confidence in DAO, so moving to ADO is not something I’m in a hurry to do…

I don’t want to keep the data any longer than the code is running for, if I can’t do this purely in VBA, can I create a new table, populate it with the file name, then delete it after I’ve used the data? Or is this going to be more difficult than learning the dark arts of ADO???

Thanks

Ian

Well, the demo looks nice, however, it’s taken me a while to gain any level of confidence in DAO, so moving to ADO is not something I’m in a hurry to do…

I don’t want to keep the data any longer than the code is running for, if I can’t do this purely in VBA, can I create a new table, populate it with the file name, then delete it after I’ve used the data? Or is this going to be more difficult than learning the dark arts of ADO???

Thanks

Ian

I just tried this simple code in the on gotfocus event, you can use it in other ways.

Dim strVar As String
Dim strOutput As String

strVar = 0

If Me.Text0 0 Then
strVar = strVar + 1
End If
If Me.Text2 0 Then
strVar = strVar + 1
End If
If Me.Text4 0 Then
strVar = strVar + 1
End If
If Me.Text6 0 Then
strVar = strVar + 1
End If

strOutput = (Me.Text0.Value + Me.Text2.Value + Me.Text4.Value + Me.Text6.Value) / strVar

Me.Text8 = strOutput

The text boxes just hold values, text8 give the average when it gets the focus.

I just tried this simple code in the on gotfocus event, you can use it in other ways.

Dim strVar As String
Dim strOutput As String

strVar = 0

If Me.Text0 0 Then
strVar = strVar + 1
End If
If Me.Text2 0 Then
strVar = strVar + 1
End If
If Me.Text4 0 Then
strVar = strVar + 1
End If
If Me.Text6 0 Then
strVar = strVar + 1
End If

strOutput = (Me.Text0.Value + Me.Text2.Value + Me.Text4.Value + Me.Text6.Value) / strVar

Me.Text8 = strOutput

The text boxes just hold values, text8 give the average when it gets the focus.

Hans

Thanks, this worked, any idea why it worked?

Ian

Hans

Thanks, this worked, any idea why it worked?

Ian

Hi

You can export data to Excel, from Access, in a number of ways, transferspreadsheet, DoCmd.OutputTo, or automation. Which way you do depends on exactly how you want to manipulate the data as it leaves Access.

Also, why use Excel and Access? Why not just allow the user to select specific data for viewing through the Access database?

Ian

Hi

You can export data to Excel, from Access, in a number of ways, transferspreadsheet, DoCmd.OutputTo, or automation. Which way you do depends on exactly how you want to manipulate the data as it leaves Access.

Also, why use Excel and Access? Why not just allow the user to select specific data for viewing through the Access database?

Ian