Correct Syntax

[GLT]

Can anyone advise the correct syntax for the following line?

strCriteria = "rst1![""" & strFeild & "]" & "" = strReadFeild

Any help is always greatly appreciated...

Cheers,
GLT

 

[akphidelt]

Try

"rst1![" & strField & "]='" & strReadFeild & "'"

The last part has " ' " just for your information

 

[Klatuu]

Not enough info to give a completely correct answer, but based on what you
posted and assuming strFeild is a text field:

strCriteria = "rst1![" & strFeild & "] = """ & strReadFeild & """"

But,typically, you don't include the recordset name you are trying to
filter, so it probably should be:

strCriteria = "[" & strFeild & "] = """ & strReadFeild & """"

 

[GLT]

Hi Gents,

Thanks for your assistance here - its been invaluble...

How do I execute strCriteria - I tried this:

rst1.AddNew
rst1![DateStamp] = strDate
rst1![FileNo] = strFileNo
strCriteria

The above gives me a compile error - do I need to put the rst1. in front of
strCriteria?

Cheers,
GLT.

Not enough info to give a completely correct answer, but based on what you
posted and assuming strFeild is a text field:

strCriteria = "rst1![" & strFeild & "] = """ & strReadFeild & """"

But,typically, you don't include the recordset name you are trying to
filter, so it probably should be:

strCriteria = "[" & strFeild & "] = """ & strReadFeild & """"

--
Dave Hargis, Microsoft Access MVP

Can anyone advise the correct syntax for the following line?

strCriteria = "rst1![""" & strFeild & "]" & "" = strReadFeild

Any help is always greatly appreciated...

Cheers,
GLT

 

[Klatuu]

How about you tell us what you are trying to do, and we can help with the how.
You don't normally set a filter on an open recordset. It is done when you
open the recordset, so what you are doing will not work.
--
Dave Hargis, Microsoft Access MVP

Hi Gents,

Thanks for your assistance here - its been invaluble...

How do I execute strCriteria - I tried this:

rst1.AddNew
rst1![DateStamp] = strDate
rst1![FileNo] = strFileNo
strCriteria

The above gives me a compile error - do I need to put the rst1. in front of
strCriteria?

Cheers,
GLT.

Not enough info to give a completely correct answer, but based on what you
posted and assuming strFeild is a text field:

strCriteria = "rst1![" & strFeild & "] = """ & strReadFeild & """"

But,typically, you don't include the recordset name you are trying to
filter, so it probably should be:

strCriteria = "[" & strFeild & "] = """ & strReadFeild & """"

--
Dave Hargis, Microsoft Access MVP

Can anyone advise the correct syntax for the following line?

strCriteria = "rst1![""" & strFeild & "]" & "" = strReadFeild

Any help is always greatly appreciated...

Cheers,
GLT

 

[GLT]

Hi Klatuu,

I am not trying to use a filter.

I am using strCriteria to insert records into a table, reading from another
table.

What i am trying to do is eliminate column 1, and change column 2 to rows
instead of a column.

Here is my earlier post:

Hi,

I have two record sets open - i am trying to take data from the first record
set and add it to another.

In my first recordset, there are two columns (for example):

Col 1 Col 2

JOB JOB15XY
JOBNAME STRDMTTYS
JOBID 134565

JOB .....
JOBNAME .....
JOBID .....

etc

Col 1 repeats many times over - its a waste of space having JOB, JOBNAME and
JOBID repeated somany times.

What I am tring to do is use the values in col 1 as headings in a new table,
and copy the details of col2 - so the recordset will look like this:

JOB JOBNAME JOBID
JOB15XY STRDMTTYS 134565

The code i am trying to use is as follows:

rst1.AddNew
rst1![DateStamp] = strDate
rst1![FileNo] = strFileNo
rst1!strReadFeild1 = strFeild2 <----- Item not found error

strReadFeild1 is a string varible which reads col 1 value from the first
recordset.
If I try and use the string in second recordset, an Item Not Found Error is
returned.

How about you tell us what you are trying to do, and we can help with the how.
You don't normally set a filter on an open recordset. It is done when you
open the recordset, so what you are doing will not work.
--
Dave Hargis, Microsoft Access MVP

Hi Gents,

Thanks for your assistance here - its been invaluble...

How do I execute strCriteria - I tried this:

rst1.AddNew
rst1![DateStamp] = strDate
rst1![FileNo] = strFileNo
strCriteria

The above gives me a compile error - do I need to put the rst1. in front of
strCriteria?

Cheers,
GLT.

Not enough info to give a completely correct answer, but based on what you
posted and assuming strFeild is a text field:

strCriteria = "rst1![" & strFeild & "] = """ & strReadFeild & """"

But,typically, you don't include the recordset name you are trying to
filter, so it probably should be:

strCriteria = "[" & strFeild & "] = """ & strReadFeild & """"

--
Dave Hargis, Microsoft Access MVP


:

Can anyone advise the correct syntax for the following line?

strCriteria = "rst1![""" & strFeild & "]" & "" = strReadFeild

Any help is always greatly appreciated...

Cheers,
GLT

 

[GLT]

This is what I am trying to acheive - I am trying to hunt this down on google
(and on here in other posts and I cannot find anything)...

Writing to A Recordset - Using a VARIABLE

rst!(VARIABLE)

Hi Klatuu,

I am not trying to use a filter.

I am using strCriteria to insert records into a table, reading from another
table.

What i am trying to do is eliminate column 1, and change column 2 to rows
instead of a column.

Here is my earlier post:

Hi,

I have two record sets open - i am trying to take data from the first record
set and add it to another.

In my first recordset, there are two columns (for example):

Col 1 Col 2

JOB JOB15XY
JOBNAME STRDMTTYS
JOBID 134565

JOB .....
JOBNAME .....
JOBID .....

etc

Col 1 repeats many times over - its a waste of space having JOB, JOBNAME and
JOBID repeated somany times.

What I am tring to do is use the values in col 1 as headings in a new table,
and copy the details of col2 - so the recordset will look like this:

JOB JOBNAME JOBID
JOB15XY STRDMTTYS 134565

The code i am trying to use is as follows:

rst1.AddNew
rst1![DateStamp] = strDate
rst1![FileNo] = strFileNo
rst1!strReadFeild1 = strFeild2 <----- Item not found error

strReadFeild1 is a string varible which reads col 1 value from the first
recordset.
If I try and use the string in second recordset, an Item Not Found Error is
returned.

How about you tell us what you are trying to do, and we can help with the how.
You don't normally set a filter on an open recordset. It is done when you
open the recordset, so what you are doing will not work.
--
Dave Hargis, Microsoft Access MVP

Hi Gents,

Thanks for your assistance here - its been invaluble...

How do I execute strCriteria - I tried this:

rst1.AddNew
rst1![DateStamp] = strDate
rst1![FileNo] = strFileNo
strCriteria

The above gives me a compile error - do I need to put the rst1. in front of
strCriteria?

Cheers,
GLT.

:

Not enough info to give a completely correct answer, but based on what you
posted and assuming strFeild is a text field:

strCriteria = "rst1![" & strFeild & "] = """ & strReadFeild & """"

But,typically, you don't include the recordset name you are trying to
filter, so it probably should be:

strCriteria = "[" & strFeild & "] = """ & strReadFeild & """"

--
Dave Hargis, Microsoft Access MVP


:

Can anyone advise the correct syntax for the following line?

strCriteria = "rst1![""" & strFeild & "]" & "" = strReadFeild

Any help is always greatly appreciated...

Cheers,
GLT

 

[John W. Vinson]

In my first recordset, there are two columns (for example):

Col 1 Col 2

JOB JOB15XY
JOBNAME STRDMTTYS
JOBID 134565

JOB .....
JOBNAME .....
JOBID .....

etc

Col 1 repeats many times over - its a waste of space having JOB, JOBNAME and
JOBID repeated somany times.

What I am tring to do is use the values in col 1 as headings in a new table,
and copy the details of col2 - so the recordset will look like this:

JOB JOBNAME JOBID
JOB15XY STRDMTTYS 134565

The code i am trying to use is as follows:

rst1.AddNew
rst1![DateStamp] = strDate
rst1![FileNo] = strFileNo
rst1!strReadFeild1 = strFeild2 <----- Item not found error

strReadFeild1 is a string varible which reads col 1 value from the first
recordset.
If I try and use the string in second recordset, an Item Not Found Error is
returned.

You've got trouble.

How can you reliably associate one JOB record with its corresponding JOBNAME
and JOBID value, if these are the only fields in your table? A Table has no
usable order; if there is nothing to connect JOB JOB15XY with JOBIC 134565 you
have no guarantee that they'll be linked up!

Your code is clearly incomplete, or wrong; you talk about reading records from
the first recordset, but your code is *adding* records to the new recordset
(AddNew), and referencing fieldnames which don't match your example; and
finally, nowhere in the code that you have posted do you define the values of
strDate or strField2.

Could you please post the actual SQL or recordset definition of your first
recordset, and the actual code that you are using?

 

[John W. Vinson]

This is what I am trying to acheive - I am trying to hunt this down on google
(and on here in other posts and I cannot find anything)...

Writing to A Recordset - Using a VARIABLE

rst!(VARIABLE)

I don't know if this will help, given the concerns in my other message, but
you're mixing syntax here. There are two ways to reference a field in a
recordset:

something = rst!myfieldname

or

rst("myfieldname")

Or, more to the point,

Dim strFieldname As String
strFieldname = "myfieldname"
something = rst(strFieldname)

Note - no ! in the last line.

 

[GLT]

Hi John,

Thanks for your response to this - I think I can now start to see the
trouble Im in with this list - it looked like a very simple operation to
perform but now not so simple

Here is the code that I tried to use:

Sub LogFiles()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim strDate As String
Dim strFileNo As String
Dim strFeild As String
Dim strReadFeild As String
Dim strCriteria As String

Set db = CurrentDb()
Set rst = CurrentDb.OpenRecordset("tbl_ParsedLogFileDetails")
Set rst1 = CurrentDb.OpenRecordset("tbl_BackupLogStats")

Countno = rst.RecordCount

If rst.RecordCount = 0 Then
rst.Close
Set rst = Nothing
Set db = Nothing
Else
With rst
.MoveLast
.MoveFirst
Do While Not .EOF

rst.Edit
strDate = rst!DateStamp
strFileNo = rst![FileNo]
strFeild = rst![Feild1]
strReadFeild = rst![Feild2]

With rst1

'strCriteria = "rst1![" & strFeild & "] = """ &
strReadFeild & """"

rst1.AddNew
rst1![DateStamp] = strDate
rst1![FileNo] = strFileNo
rst1!strCriteria

End with

LOOP

END WITH

END IF

END SUB


Is there any other way I can manipulate this data to so it will list in a
proper table format?

Cheers,
GLT.

In my first recordset, there are two columns (for example):

Col 1 Col 2

JOB JOB15XY
JOBNAME STRDMTTYS
JOBID 134565

JOB .....
JOBNAME .....
JOBID .....

etc

Col 1 repeats many times over - its a waste of space having JOB, JOBNAME and
JOBID repeated somany times.

What I am tring to do is use the values in col 1 as headings in a new table,
and copy the details of col2 - so the recordset will look like this:

JOB JOBNAME JOBID
JOB15XY STRDMTTYS 134565

The code i am trying to use is as follows:

rst1.AddNew
rst1![DateStamp] = strDate
rst1![FileNo] = strFileNo
rst1!strReadFeild1 = strFeild2 <----- Item not found error

strReadFeild1 is a string varible which reads col 1 value from the first
recordset.
If I try and use the string in second recordset, an Item Not Found Error is
returned.

You've got trouble.

How can you reliably associate one JOB record with its corresponding JOBNAME
and JOBID value, if these are the only fields in your table? A Table has no
usable order; if there is nothing to connect JOB JOB15XY with JOBIC 134565 you
have no guarantee that they'll be linked up!

Your code is clearly incomplete, or wrong; you talk about reading records from
the first recordset, but your code is *adding* records to the new recordset
(AddNew), and referencing fieldnames which don't match your example; and
finally, nowhere in the code that you have posted do you define the values of
strDate or strField2.

Could you please post the actual SQL or recordset definition of your first
recordset, and the actual code that you are using?

 

[John W. Vinson]

Thanks for your response to this - I think I can now start to see the
trouble Im in with this list - it looked like a very simple operation to
perform but now not so simple

If the original source of the data is a Text file (as the LogFiles name seems
to suggest) you may be OK. If it's an Access table then you will need a fair
modicum of luck!

What are the structures and actual fieldnames of your two tables? Where does
tblParsedLogFile_Details come from - what does the "parsing" and how is the
data entered?

Your trying to "execute" strCriteria is simply off on the wrong track. You'll
need to either build a single-record append query and execute it, or edit the
second recordset three times, once for each field, after the AddNew method -
but I don't know what the appropriate fieldnames are in the first recordset.

 

[GLT]

Hi John,

Thanks again for your response.

Source of table 1:

I have about 50 text (log) files that I read into a memo feild (one memo
feild per log).

I then do the following:

Scan each memo feild line by line
Seperate each line into sections and break out each section into its own
feild - this is how table 1 is created.

Table 1 has the following feilds:

<DATE STAMP> <FILE NO> <FEILD1> <FEILD2>

Feild 1 contains the following (its just a sample):

FEILD1
FEILD2


Err/War
Warning #1
Job No <-----------Section 1
45645645
Job ID
e78ejd
Description
BACKUP
Workstation
Source
etc
Target
Session
Start Time
Session Method
Err/War
Err/War
Total Directories
Total File(s)
Total Skip(s)
Total Size (Disk)
Total Size (Media)
Elapsed Time
Average Throughput
Total Error(s)/Warning(s)
Session Status
Job No <------------Section 2
Job ID
Workstation
Source
Target
Session
Start Time
Session Method
Err/War
Err/War
Total Directories
Total File(s)
Total Skip(s)
Total Size (Disk)
Total Size (Media)
Elapsed Time
Average Throughput
Total Error(s)/Warning(s)
Session Status
Job No <--------Section 3
Job ID

Feild 2 contains the respective values from feild 1 - the entries in feild
one go from appear to start at Job No - but in each (i have lableed them
sections above) section, sometimes entries do not appear, so I can rely on
the above list being repetitivly the same - I need to analyse each value,
then place its partner (which is in column Feild 2) in a seperate table...

Hope this make sense....

Cheers,
Geoff.

 

[David W. Fenton]

Can anyone advise the correct syntax for the following line?

strCriteria = "rst1![""" & strFeild & "]" & "" = strReadFeild

Any help is always greatly appreciated...

It's hardly ever the case that you need to step through a recordset
to do an update like this. It's likely that a SQL UPDATE would be
both much easier and orders of magnitude more efficient.

And require no VBA coding.

 

[GLT]

Hi David,

Thanks for your response - what I am in fact trying to do is import a flat
file into a table - from what I understand queries cannot do this ...

Can anyone advise the correct syntax for the following line?

strCriteria = "rst1![""" & strFeild & "]" & "" = strReadFeild

Any help is always greatly appreciated...

It's hardly ever the case that you need to step through a recordset
to do an update like this. It's likely that a SQL UPDATE would be
both much easier and orders of magnitude more efficient.

And require no VBA coding.

 

[John W. Vinson]

Hi John,

Thanks again for your response.

You'll need some VBA to step through the file *as you import it* - stashing it
in a memo field and passing it on to a table (losing sequence information in
the process) are just going to get in the way.

I'm pretty busy with clients right now and will try to get to this by the end
of the week, but can't promise anything.

 

[David W. Fenton]

what I am in fact trying to do is import a flat
file into a table - from what I understand queries cannot do this
...

Do you need to program the import so that it runs repeatedly? If so,
you should look at DoCmd.TransferDatabase, which is the programmatic
command to do the same thing that the File | Import command does.

I don't quite see how walking a recordset does anything at all to
import data.

 

[GLT]

Hi David,

Thanks for your response...

Yes I need to run import repeatedly, but my data is listed sequentialy, each
feild is listed line by line - not column by column.

From the searching that I have done so far, I need to get something like sql
loader or a pearl script to read each line in and then build the table.

As far as i know (I have tried the import command) it will only import data
that is already in a column format...

If you know any easier way to sort through this data then I'm all ears...

Cheers,
GLT.

 

[John W. Vinson]

From the searching that I have done so far, I need to get something like sql
loader or a pearl script to read each line in and then build the table.

What I'm going to suggest - when I have time to work on it! - is to use the
file-handling tools in VBA to read the *text file* line by line and load the
table.

 

[David W. Fenton]

What I'm going to suggest - when I have time to work on it! - is
to use the file-handling tools in VBA to read the *text file* line
by line and load the table.

I think I'd be likely to read the input text file into a new file,
then import that in one fell swoop into Access. The reason is that
this would be much more efficient that updating a record of a
recordset as each line is constructed from the input file.

 

[John W. Vinson]

I think I'd be likely to read the input text file into a new file,
then import that in one fell swoop into Access. The reason is that
this would be much more efficient that updating a record of a
recordset as each line is constructed from the input file.

David, the problem with that is that each record in the actual table
corresponds to MULTIPLE RECORDS in the input file - multiple records which
have no common field and no way to link them!

His input file has one record *per field*, with nothing except a fieldname and
a field value. The only thing tying them together is the sequential position
in the text file - and, unless I'm missing something, that information would
be irretrievably lost on loading into a JET table. You could *hope* that the
recordset keeps its order but that's not what I would expect.