Blank fields

[Lewy]

Odd thing started happening. I query a table with a check mark in all the fields. In the answer table in random records one field is blank, yet in the table that is being queried there is a value in that field! There doesn't appear to be a pattern to the missing fields.

Any thoughts?

Thanks,

Lewy

 

[TonyMcGuire]

Are you doing a check, or a checkPlus?

Checkplus will get all records regardless of whether there are multiple of the same values for a particular field.

Check will only retrieve the first instance of any given record where any field's value is the same.

I may be misunderstanding the question, so please fire away if you can enlighten me as to the question - if my answer sounds wicky.


Tony McGuire
"It's not about having enough time; we have the rest of our lives. It's about priorities.

 

[Lewy]

Thanks for the reply Tony. the field in question is only checked so that it appears in the answer table. The value is used in a mail merge to indicate to a client how long ago their last visit to our practice was. ie 6/12/18/24 months. The answer table is not dependent upon this value but on the value of another field within the table. ie all clients that are due to return during say March 2009. so the query gets all the records from the client table that are due a reminder letter this month. the answer table is then merged with a letter in microsoft word and mailed out. The last 2/3 months the recall period field has been empty in the answer table but not in the client table.

does that make sense?

thanks,

Lewy

 

[Lewy]

Have also just found there are records missing from one of my tables. Corruption somewhere? Should I run Tutility?

Thanks,

Lewy

 

[TonyMcGuire]

I'm sorry. The situation is getting less understandable rather than more understandable.

I'm afraid I understand neither the problem nor the solution sought.

Tony McGuire
"It's not about having enough time; we have the rest of our lives. It's about priorities.

 

[Lewy]

Sorry Tony,
I always find it hard to explain a problem.

The problem is that when I run a saved query, in one of the fields in the answer table has a blank field, yet in the table I have queried, there is a value.

Table Structure:

Primary Key Customer_No N
ExamDate D
Title A6
FirstName A12
LastName A20
NoandStreet A56
Subburb A20
Town A25
Postcode A8
Mobile A20
WorkTel A15
HomeTel A15
DoB D
Doctor A15
TestType A20
Amount $
VAmount $
OldPx A3
SeenBy A25
ListNo A16
Required A10
Notes M50
RecallPeriod N
RecallDate D
Collected D
EMail A30
Organisation A15
Family S
RecallType A50
Query
ANSWER: RIV:ANSWER.DB

SORT: Cust1.DB->"Last Name", Cust1.DB->"Customer_No", Cust1.DB->"ExamDate",
Cust1.DB->"Title", Cust1.DB->"First Name", Cust1.DB->"No and Street", Cust1.DB->"Subburb",
Cust1.DB->"Town", Cust1.DB->"Postcode", Cust1.DB->"Mobile", Cust1.DB->"Work Tel",
Cust1.DB->"Home Tel", Cust1.DB->"DoB", Cust1.DB->"Doctor", Cust1.DB->"Test Type",
Cust1.DB->"VAmount", Cust1.DB->"OldPx", Cust1.DB->"Seen By", Cust1.DB->"Required",
Cust1.DB->"RecallPeriod", Cust1.DB->"RecallDate", Cust1.DB->"E Mail", Cust1.DB->"Collected"

Cust1.DB | Customer_No | ExamDate | Title | First Name | Last Name |
| Check | Check | Check | Check | CheckPlus |

Cust1.DB | No and Street | Subburb | Town | Postcode | Mobile | Work Tel |
| Check | Check | Check | Check | Check | Check |

Cust1.DB | Home Tel | DoB | Doctor | Test Type | VAmount |
| Check | Check <=today-(16*365) | Check | Check | Check |

Cust1.DB | OldPx | Seen By | Required | Notes | RecallPeriod |
| Check | Check | Check | Check | Check |

Cust1.DB | RecallDate | Collected | E Mail | Recall Type |
| Check ../03/09 | Check | Check | Not Co.. |

EndQuery

This will result in some records in the answer table having nothing in the 'RecallPeriod' field, yet there is a value in the Cust1 table for that record. The 2nd posting was an observation that in a different table some of the records have dissapeared. My question is why has this happenned and is my copy of Paradox10 corrupt and if so what dio I need to do to put it right?

I hope this is now clear.

Many many thanks, Lewy

 

[TonyMcGuire]

For 'recalldate', instead of typing the date in use a declared var, stRcDate. As well, declare stDob as a string and assign it a value of the dob field in the query.

var
stDob,
stRcDate string
endvar

stRcDate="../03/09"
stDob="<=today-(16*365)"



Instead of 'check', please use 'checkplus' for each field.

Note that you are using 'checkplus' for [last name] field as you presented to me.

qvar=Query

Cust1.DB | Customer_No  | ExamDate   | Title     | First Name | Last Name     |
         | checkPlus    | checkPlus  | checkPlus | checkPlus  | checkPlusPlus |

Cust1.DB | No and Street | Subburb    | Town      | Postcode  | Mobile    | Work Tel   |
         | checkPlus     | checkPlus  | checkPlus | checkPlus | checkPlus | checkPlus  |

Cust1.DB | Home Tel     | DoB              | Doctor     | Test Type | VAmount    |
         | checkPlus    | checkPlus ~stDob | checkPlus  | checkPlus | checkPlus  |

Cust1.DB | OldPx      | Seen By     | Required     | Notes      | RecallPeriod |
         | checkPlus  | checkPlus   | checkPlus    | checkPlus  | checkPlus    |

Cust1.DB | RecallDate          | Collected | E Mail     | Recall Type |
         | checkPlus ~stRcDate | checkPlus | checkPlus  | Not Co..    |

EndQuery
qvar.executeqbe(":priv:answer.db")

check is check 'in ascending order'. checkplus is 'give them all'.

Using tilde (~) variables will often unconfuse confusion where open dates or date variables are concerned.

Does that work?



Tony McGuire
"It's not about having enough time; we have the rest of our lives. It's about priorities.

 

[Lewy]

Thanks Tony,

I will try what you have suggested and will let you know.

Once again, many thanks and have a good weekend.

Lewy