SQL string not working

  • Thread starter Thread starter Jim Hill
  • Start date Start date
J

Jim Hill

Hello,
The following string does not append 5 rows to the
TableResult, it appends all the rows from MyTable. I need
to appand 5 current row.
What is the problem?

sqlString = " INSERT INTO TableResult" & _
" SELECT TOP 5 MyTable.* " & _
" FROM MyTable " & _
" WHERE (((MyTable.locId) = '12') And ((MyTable.myDate)
= '20030101' And (MyTable.myDate) <= '20031008')) " & _
Click to expand...
" ORDER BY MyTable.myDate DESC "

vDb.Execute sqlString
 
You need to surround your date fields with # symbols:

Where MyTable.myDate=#123456# or MyTable.myDate>#121212#

Also, surround your subselect with parenthesis:

INSERT INTO myTable (SELECT * FROM MyOtherTable WHERE
MyOtherTable.MyDate=#1-1-2000#)
 
myDate is a string and parenthesis did not work, it gave
Syntax error in INSERT INTO statement. (Error 3134)
 
Jet requires date values to be in mm/dd/yyyy (or mm/dd/yy) format if you
wish to use the date expression in a criterion. Thus, you need to
reconfigure your date expressions into this format and then use them in the
criteria statement.

Try this (assumes that your date string is in the format yyyymmdd):

sqlString = " INSERT INTO TableResult" & _
" SELECT TOP 5 MyTable.* " & _
" FROM MyTable " & _
" WHERE (((MyTable.locId) = '12') And " & _
" ((MyTable.myDate) BETWEEN '#' & " & _
" Mid('20030101', 5, 2) & '/' & Right('20030101', 2) " & _
" & '/' & Left('20030101', 4) & '#' AND '#' & " & _
" Mid('20031008', 5, 2) & '/' & Right('20031008', 2) " & _
" & '/' & Left('20031008', 4) & '#')) " & _
" ORDER BY MyTable.myDate DESC "

Alternatively, you could put a function in a regular module that will
properly parse the date info into the right format and then call that
function from the query.
 
Thanks for the reply but myDate is actually a text field
in the table with YYYYMMDD format and I am trying to sort
it with DESC and bring top 5. So it is not a date
problem, have you tried insert into with select top
statement. SQL is working but not bringing top 5.
 
How many rows are returned by the query

SELECT TOP 5 MyTable.*
FROM MyTable
WHERE (((MyTable.locId) = '12')
And ((MyTable.myDate) >= '20030101'
And (MyTable.myDate) <= '20031008'))
ORDER BY MyTable.myDate DESC
 
I had to use "Set vBsResult = vDb.OpenRecordset
(sqlString)" instead of "vDb.Execute sqlString" (which
does not work for select statement)
It returned 22 records which was the case before. Is
there any other seting I should take care for "TOP" to
make it effective? I have never used it before.
 
Hey Doug,
Thanks for reply. It seems last 22 myDate is the same and
SQL brings all of them even if TOP 5 is there. I used
another field in the ORDER BY and it works fine now. Is
this case known or I am the only one who is experiencing
this?
Thanks,
Jim.
 
If there's a tie using TOP, you'll get all of the records that qualify since
Access has no way of knowing how to break the tie. That's really the only
possible implementation they could have used.
 
Doug,
Thanks. That is fine. I have only one problem left. This
Access application will be in a terminal server and users
will access to this terminal server and will run it.
There will be a form and over this form users will update
the common database. I am actually new in access, and I
am wondering how should I implement it? While different
users are running the same access application in parallel
I am sure they will get access violation, what is the way
of giving access to many users in parallel?
Thanks,
Jim.
 
As long as each user has his/her own copy of the front-end, they should all
be able to work with the same back-end concurrently. Yes, you might run into
the odd record locking problem, but it really isn't that common that two
users will try to update at exactly the same time.
 
Hey thanks,
How can I have a copy of front-end for each user. This is
an Access Form and they are luanching the same dbf file
to get the form, so the same user interface. What should
I do?
Thanks,
Jim.
 
Not 100% sure what you're asking.

Access uses .MDB files, not .DBF files (.DBF is dBase, I believe). If that
was a typo on your part, and you are actually talking about a MDB file, make
sure that you have split the application into a front-end (containing the
queries, forms, reports, macros and modules), linked to a back-end
(containing just the tables). Each person should have their own copy of the
front-end, all of which should link to the same back-end.
 
Back
Top