New to SQL

I have a table with a field that is 14 characters long. I have created two more columns with in the same table. I wish to parse each record taking the 14 character field and storing the first 10 characters in one of the newly created fields and take the next 4 characters and store them in the other newly created fields.

Can someone tell me how to do that?Look in BOL (Books Online) at the Left and Right functions.|||I can do the left and right functions. I just don't understand the looping through the records and updating the other fields.|||Run an update query:

UPDATE TableName
SET SecondField = Left(FirstField,10),
ThirdField = Right(FirstField,4)|||Thanks. I was thinking this was going to be more complicated.|||Of course the real question is should you have the other fields in the table, since their values can always be calculated from the first. Generally speaking the answer would be no.

New to CR

I'm coming in on the back end of a project and I don't understand the following record selection formula. Would someone please explain what it's doing?

if {?@.rptfilter} = 0 then
(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 1 then
({CAS_SP_SolnLosses;1.Contractor} = "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} = "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 2 then
({CAS_SP_SolnLosses;1.Contractor} <> "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} <> "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})This is not an answer to your question, but at least in earlier versions (up to version 8.5) Crystal reports is not able to create an SQL stement based on control structures (If-Then-Else) instead you should convert the record selection formula to a boolean statement using ands and ors and braces. What happens if you don't do this is that Crystal loads the entire answer set to your computer and makes the record selection locally. This has at least two major disadvantages:
1. The query is processed on the database server as a full table join and the query can not make use of any indexes. This can cause performance problems to any DBMS system
2. You cause a lot of network traffic moving unneeded data over the network

This has one implication, which is poor performance.

What concerns your code I have a few suggestions:

(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
could be removed in my opinion, because it compares a field to both null and not null

after this you basically create different joins based on different values of
{?@.rptfilter}

Hope this gives you some clues.

- Jukka|||This is not an answer to your question, but at least in earlier versions (up to version 8.5) Crystal reports is not able to create an SQL stement based on control structures (If-Then-Else) instead you should convert the record selection formula to a boolean statement using ands and ors and braces. What happens if you don't do this is that Crystal loads the entire answer set to your computer and makes the record selection locally. This has at least two major disadvantages:
1. The query is processed on the database server as a full table join and the query can not make use of any indexes. This can cause performance problems to any DBMS system
2. You cause a lot of network traffic moving unneeded data over the network

This has one implication, which is poor performance.

What concerns your code I have a few suggestions:

(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
could be removed in my opinion, because it compares a field to both null and not null

after this you basically create different joins based on different values of
{?@.rptfilter}

Hope this gives you some clues.

- Jukka

I agree that line you mentioned could be deleted. My problem is understanding the syntax of this *ugly* formula. Unfortunately, the developer that wrote this is long gone and, like I mentioned earlier, it's been dumped in my lap. I've looked over the .pdf's on the CD hoping to find some help, no luck. Are there any other sources which would help me get my head around understanding what this code is doing?

TIA

BTW, is there a way to step thru the formula code with a debugger?|||Hello,

I'm doing project using Access as backend and VB6 FrontEnd. I have the following problem during compilation. I'm using Runtime Activex lib of CR 8.5.

Error: Method or Data member not found

===> Private Sub mnu_stock_Click()
With Form1.CrystalReport1
.DataFiles(0) = App.Path & "\MyDB.MDB"
.ReportFileName = App.Path & "\Report\RPT_AVA_STOCK.rpt"
.username = "Admin"
.Password = "1010101010" & Chr(10) & "1010101010"
.Action = 1
.PageZoom (100)
End With
End Sub

Thank you,

Regards,

Niranjan Dixit|||I agree that line you mentioned could be deleted. My problem is understanding the syntax of this *ugly* formula. Unfortunately, the developer that wrote this is long gone and, like I mentioned earlier, it's been dumped in my lap. I've looked over the .pdf's on the CD hoping to find some help, no luck. Are there any other sources which would help me get my head around understanding what this code is doing?

TIA

BTW, is there a way to step thru the formula code with a debugger?

Here is a try (the syntax is slightly different to what I'm used to using Oracle):
You have the following parameters in your report controlling the outcome:
rptfilter
typeid

comments are marked with a "-"

Fuurther I would still make sure that I have all parenthesis in the right places. I would include an extra round of parenthesis where rptfilter changes values

and the code it self:
if {?@.rptfilter} = 0 then
- rpt filter = 0

(isnull({CAS_SP_SolnLosses;1.Contractor}) or not isnull({CAS_SP_SolnLosses;1.Contractor}))
- This section does nothing

and
(if {?@.typeid} = 0 then
- rptfilter = 0 and typeid = 0

ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
- When rptfilter = 0 and typeid = 0 then only record having CAS_SP_SolnLosses;1.Typevalue = "ANY" are included

else
if {?@.typeid} = 1 then
- rptfilter = 0 and typeid = 1

{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
- When rptfilter = 0 and typeid = 1 then is joined to CAS_SP_SolnLosses;1.Typevalue = {CAS_SP_SolnLosses;1.Region}, i.e. values have to be equal
- Similar for typeid 2 - 8

else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else

if {?@.rptfilter} = 1 then
- rptfilter = 1

({CAS_SP_SolnLosses;1.Contractor} = "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} = "CAJV")
- rptfilter = 1 and CAS_SP_SolnLosses;1.Contractor = "ARORA" or CAS_SP_SolnLosses;1.Contractor "CAJV"

and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
- This is similar to above except that rptfilter has value 1 and further below value 2

else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})
else
if {?@.rptfilter} = 2 then
({CAS_SP_SolnLosses;1.Contractor} <> "ARORA" OR {CAS_SP_SolnLosses;1.Contractor} <> "CAJV")
and
(if {?@.typeid} = 0 then
ucase({CAS_SP_SolnLosses;1.Typevalue}) = "ANY"
else
if {?@.typeid} = 1 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.TNex}
else
if {?@.typeid} = 2 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Region}
else
if {?@.typeid} = 3 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Agency}
else
if {?@.typeid} = 4 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Site}
else
if {?@.typeid} = 5 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.AVP}
else
if {?@.typeid} = 6 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.RM}
else
if {?@.typeid} = 7 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.Recruiter}
else
if {?@.typeid} = 8 then
{CAS_SP_SolnLosses;1.Typevalue} = {CAS_SP_SolnLosses;1.ES})|||Here is a try (the syntax is slightly different to what I'm used to using Oracle):
You have the following parameters in your report controlling the outcome:
rptfilter
typeid

comments are marked with a "-"

Fuurther I would still make sure that I have all parenthesis in the right places. I would include an extra round of parenthesis where rptfilter changes values
<snip>

Thanks for your help!

new records are not updateable

Hi,
In access, a form creates a new record. This record is not updateable -
message says that someone else is editing the record. However, in EM, the
record is updatable.
What a I doing wrong?
Do I need to supply further info?
cheers,
NEILNeil,
Might have better luck on this one by posting to the ACCESS NG. However,
you might check the 'Default open mode' for the .mdb on the Advanced tab of
Tools-Options in Access. (i.e., shared or exclusive)
HTH
Jerry
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:di3k5d$ja$1$830fa17d@.news.demon.co.uk...
> Hi,
> In access, a form creates a new record. This record is not updateable -
> message says that someone else is editing the record. However, in EM, the
> record is updatable.
> What a I doing wrong?
> Do I need to supply further info?
> cheers,
> NEIL
>|||When you edit data in a data bound form in MS Access, it uses a client side
cursor. Perhaps you need to confirm that the CursorType property is defined
as Dynamic so inserts, updates and deletions by other users are visible:
http://msdn.microsoft.com/library/d...sortypeenum.asp
Also, perhaps use of the Recordset.Resync method:
http://msdn.microsoft.com/library/d...>
doresync.asp
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:di3k5d$ja$1$830fa17d@.news.demon.co.uk...
> Hi,
> In access, a form creates a new record. This record is not updateable -
> message says that someone else is editing the record. However, in EM, the
> record is updatable.
> What a I doing wrong?
> Do I need to supply further info?
> cheers,
> NEIL
>

New record results in blank foreign key in subform

Hello,

I have a form with a subform. The mainform has a primary key ([ID]) and the subform has a foreign key ([MasterID]). When new records are added in the mainform, I get an error message that says, "ODBC -- Call Failed." The error message goes on to say that it cannot insert a null value into the column [MasterID], the foreign key in my subform.

[ID] and [MasterID] are in a one-to-one relationship. The data in the main form represents information at an initial state and the data in the subform represents an end state.

The error message seems to happen before the form's Before Insert event, so I am really at a loss as to how to fix this. I am new to SQL server. The backend files are on SQL server and the front end is running on Access in an MDB file.

Thanks,
JoshSorry, I figured out what was going on - my relationship was not set up correctly. I had test data in my child table with no parent. This caused the relationship to save incorrectly.

New record in table already or not when in an INSTEAD OF trigger?

Hi, friends,
I am writting a INSTEAD OF INSERT trigger for an insert action on Users
table of our SQL Server 2000 DB.
I know there is only one record (the new inserted one) in inserted table,
but, is this new record also already in Users table? In anther word, if I do
a query in the trigger like:
DECLARE @.newUserID INT
SELECT @.newUserID = i.userID FROM inserted AS i
SELECT * FROM Users WHERE userID = @.newUserID
should I get a record back for the last SELECT statement, not yet? Any
reference paper?
Thanks for your help.No, the row that is in the inserted table in the INSTEAD OF TRIGGER is _not_
already in the base table. And if you don't explicitly insert the row in the
base table inside your trigger, the row will never be inserted. If you want
the behaviour you describe, you should use an AFTER trigger.
Jacco Schalkwijk
SQL Server MVP
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:22E7E45F-E28D-4D21-8E95-67C235390200@.microsoft.com...
> Hi, friends,
> I am writting a INSTEAD OF INSERT trigger for an insert action on Users
> table of our SQL Server 2000 DB.
> I know there is only one record (the new inserted one) in inserted table,
> but, is this new record also already in Users table? In anther word, if I
> do
> a query in the trigger like:
> DECLARE @.newUserID INT
> SELECT @.newUserID = i.userID FROM inserted AS i
> SELECT * FROM Users WHERE userID = @.newUserID
> should I get a record back for the last SELECT statement, not yet? Any
> reference paper?
> Thanks for your help.
>|||On Thu, 24 Mar 2005 14:37:04 -0800, Andrew wrote:

>I am writting a INSTEAD OF INSERT trigger for an insert action on Users
>table of our SQL Server 2000 DB.
>I know there is only one record (the new inserted one) in inserted table,
(snip)
Hi Andrew,
This is only true for single-row inserts. If you execute an INSERT INTO
... SELECT ... command, ALL new rows will be in the inserted table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

new record - new page

I made a group by TestObjectID.
When it changes, i would like to show records for that ID on the next page.
So, that with new ID, I have a new page with data.There should be 2 properties for the Group's section called 'NewPageBefore' and 'NewPageAfter' Set one of those to True.

New Crytal Report Question

Hi! I'm using Crystal Report 9. i have a problem in printing record.. when i save new record and print, it's fine! If i delete record and add a new one, still the report will print the older record..

Example: i add "ATZ" as a new record in my database then print 'OK!
then i want to clear my database and add again... so the record is deleted..
then i add "TAZ" as a new record in my database the print. the problem here is instead of "TAZ" will print still "ATZ" will comeout to the printer.

can anybody help me what is the code for this prob so that i can refresh my report. so that it will print my new record. thanks for the help...CR.Referesh=True
CrRpt.DiscardSavedData=True|||Thank you so much for your help! now i got it

How bout in this code. what i want is when i click the Print button. it will already print without prompting me. in my code when i click print, it will prompt me a message "Printer" then choose Print all or Slection...

and about printer destination without using crviewer91 control. just MyReport only. thank in advance :)
'***********************
here's my Code

Option Explicit
Dim MyReport as New CrystalReport1

Private Sub Print_CLick()

MyReport.DiscardSavedData ' = True
Set Printer = Printers(Combo1.ListIndex)
MyReport.SelectPrinter Printer.DriverName, Printer.DeviceName, Printer.Port
MyReport.PaperOrientation = crDefaultPaperOrientation
MyReport.PaperSize = crDefaultPaperSize
MyReport.PrintOut

End Sub|||Do you want to suppress the prompting?|||Yes! that's what i mean! to supress when my destination is direct to printer.

and how bout the code of creating printing destination? Thanks Madhi for the help :)|||Yes! that's what i mean! to supress. direct to printer.

and how bout the code of creating printing destination? Thanks Madhi for the help :)|||Myreport.EnablePopupMenu = False|||Hi again! :) I did! but there's and error message will apear. the message is like this "Compile Error : Method or Member Data not found" that's the error.

My Code**************

MyReport.DiscardSavedData ' = True
Set Printer = Printers(Combo1.ListIndex)
MyReport.SelectPrinter Printer.DriverName, Printer.DeviceName, Printer.Port
MyReport.PaperOrientation = crDefaultPaperOrientation
MyReport.PaperSize = crDefaultPaperSize

MyReport.EnablePopupMenu=False 'Error when i type MyReport.Enable--> No Enable PopupMenu will appear in Popup.

MyReport.EnableSelectDistinctRecords = False 'I try this also nothing happens

MyReport.EnableParameterPrompting = False 'I try this also nothing happens

MyReport.PrintOut

ATz|||CR..EnablePopupMenu = False

where CR is the Crystal Report Viewer.